Query GL Apps R12 Difference in two Years group by Months

 select * from (

  select code_combination_id, PERIOD_NUM, nvl(a3.ENTERED_YEAR_TO_DATE,0) - nvl(a4.ENTERED_YEAR_TO_DATE,0) amount from (

    SELECT bal.code_combination_id,

         (  bal.begin_balance_dr_beq

          - bal.begin_balance_cr_beq

          + bal.period_net_dr_beq

          - bal.period_net_cr_beq)

             ENTERED_YEAR_TO_DATE

             ,PERIOD_NUM

             ,apps.gl_flexfields_pkg.get_concat_description

(cc.chart_of_accounts_id,

bal.code_combination_id

)code_comb_desc,

(SELECT kfv.concatenated_segments

FROM gl_code_combinations_kfv kfv

WHERE code_combination_id = bal.code_combination_id)segments

    FROM GL_CODE_COMBINATIONS cc, GL_BALANCES bal, GL_LEDGERS gl

   WHERE     (cc.SEGMENT1 >= :P_From_Sec OR :P_From_Sec IS NULL)

         AND (cc.SEGMENT1 <= :P_To_Sec OR :P_To_Sec IS NULL)

         AND (cc.SEGMENT2 >= :P_From_Dep OR :P_From_Dep IS NULL)

         AND (cc.SEGMENT2 <= :P_To_Dep OR :P_To_Dep IS NULL)

         AND (cc.SEGMENT3 >= :P_From_Acc1 OR :P_From_Acc1 IS NULL)

         AND (cc.SEGMENT3 <= :P_To_Acc1 OR :P_To_Acc1 IS NULL) 

         AND (cc.SEGMENT4 >= :P_From_Sub_Acc OR :P_From_Sub_Acc IS NULL)

         AND (cc.SEGMENT4 <= :P_To_Sub_Acc OR :P_To_Sub_Acc IS NULL)  

         AND bal.ACTUAL_FLAG = NVL ( :P_ACTUAL_FLAG, 'A')

         AND bal.CODE_COMBINATION_ID = cc.CODE_COMBINATION_ID

         AND bal.LEDGER_ID = gl.LEDGER_ID

         AND cc.CHART_OF_ACCOUNTS_ID = 50320

         AND bal.LEDGER_ID = 2091

         AND cc.TEMPLATE_ID IS NULL

AND :P_YTD_PTD = 'YTD' 

AND PERIOD_NAME IN (SELECT XXI.PERIOD_NAME FROM  XX_I_GL_PERIODS_I XXI WHERE PERIOD_ID BETWEEN :P_FROM_PERIOD AND :P_TO_PERIOD)

GROUP BY 

          PERIOD_NUM,

         (  bal.begin_balance_dr_beq

          - bal.begin_balance_cr_beq

          + bal.period_net_dr_beq

          - bal.period_net_cr_beq)

          ,apps.gl_flexfields_pkg.get_concat_description

(cc.chart_of_accounts_id,

bal.code_combination_id

),bal.code_combination_id

  ) A3

  full join (

    SELECT bal.code_combination_id,

         (  bal.begin_balance_dr_beq

          - bal.begin_balance_cr_beq

          + bal.period_net_dr_beq

          - bal.period_net_cr_beq)

             ENTERED_YEAR_TO_DATE

             ,PERIOD_NUM

             ,apps.gl_flexfields_pkg.get_concat_description

(cc.chart_of_accounts_id,

bal.code_combination_id

)code_comb_desc,

(SELECT kfv.concatenated_segments

FROM gl_code_combinations_kfv kfv

WHERE code_combination_id = bal.code_combination_id)segments

    FROM GL_CODE_COMBINATIONS cc, GL_BALANCES bal, GL_LEDGERS gl

   WHERE     (cc.SEGMENT1 >= :P_From_Sec OR :P_From_Sec IS NULL)

         AND (cc.SEGMENT1 <= :P_To_Sec OR :P_To_Sec IS NULL)

         AND (cc.SEGMENT2 >= :P_From_Dep OR :P_From_Dep IS NULL)

         AND (cc.SEGMENT2 <= :P_To_Dep OR :P_To_Dep IS NULL)

         AND (cc.SEGMENT3 >= :P_From_Acc1 OR :P_From_Acc1 IS NULL)

         AND (cc.SEGMENT3 <= :P_To_Acc1 OR :P_To_Acc1 IS NULL) 

         AND (cc.SEGMENT4 >= :P_From_Sub_Acc OR :P_From_Sub_Acc IS NULL)

         AND (cc.SEGMENT4 <= :P_To_Sub_Acc OR :P_To_Sub_Acc IS NULL)  

         AND bal.ACTUAL_FLAG = NVL ( :P_ACTUAL_FLAG, 'A')

         AND bal.CODE_COMBINATION_ID = cc.CODE_COMBINATION_ID

         AND bal.LEDGER_ID = gl.LEDGER_ID

         AND cc.CHART_OF_ACCOUNTS_ID = 50320

         AND bal.LEDGER_ID = 2091

         AND cc.TEMPLATE_ID IS NULL

AND :P_YTD_PTD = 'YTD' 

AND PERIOD_NAME IN (SELECT XXI.PERIOD_NAME FROM  XX_I_GL_PERIODS_I XXI WHERE PERIOD_ID BETWEEN :P_FROM_PERIOD_2 AND :P_TO_PERIOD_2)

GROUP BY 

          PERIOD_NUM,

         (  bal.begin_balance_dr_beq

          - bal.begin_balance_cr_beq

          + bal.period_net_dr_beq

          - bal.period_net_cr_beq)

          ,apps.gl_flexfields_pkg.get_concat_description

(cc.chart_of_accounts_id,

bal.code_combination_id

),bal.code_combination_id

  ) A4

  using(code_combination_id, PERIOD_NUM)

)

pivot (

  SUM(AMOUNT) as YTD_DIFF

  for PERIOD_NUM in (

    '1' as Jan, '2' as Feb, '3' as Mar,

    '4' as Apr, '5' as May, '6' as Jun,

    '7' as Jul, '8' as Aug, '9' as Sep,

    '10' as Oct, '11' as Nov, '12' as Dec, '13' as Adj

  )

);

Apps R12 API to Create Supplier, Sites with interface Table

 CREATE TABLE APPS.XX_VENDOR_API_GL

(

  VENDOR_NO                   VARCHAR2(1000 BYTE),

  VENDOR_NAME                 VARCHAR2(1000 BYTE),

  VENDOR_SITE_CODE            VARCHAR2(1000 BYTE),

  ADDRESS                     VARCHAR2(1000 BYTE),

  CITY                        VARCHAR2(1000 BYTE),

  COUNTRY                     VARCHAR2(1000 BYTE),

  VENDOR_TYPE                 VARCHAR2(1000 BYTE),

  LIABILITY_ACCOUNT           VARCHAR2(1000 BYTE),

  PREPAID_ACCOUNT             VARCHAR2(1000 BYTE),

  CONTACT_PERSON              VARCHAR2(1000 BYTE),

  CONTACT_NUMBER              VARCHAR2(1000 BYTE),

  PAY_GROUP_CODE              VARCHAR2(1000 BYTE),

  PAY_DATE_BASIS_CODE         VARCHAR2(1000 BYTE),

  PAYMENT_CURRENCY_CODE       VARCHAR2(1000 BYTE),

  PAYMENT_METHOD_LOOKUP_CODE  VARCHAR2(1000 BYTE),

  PAY_TERMS                   VARCHAR2(1000 BYTE),

  TAX_CODE                    VARCHAR2(1000 BYTE),

  STATUS                      CHAR(1 BYTE),

  NUM                         NUMBER,

  INVOICE_CURRENCY_CODE       VARCHAR2(30 BYTE),

  VENDOR_ID                   NUMBER,

  VENDOR_SITE_ID              NUMBER,

  TERM_ID                     NUMBER

);

SELECT * FROM  

 XX_VENDOR_API_GL;

 

 

SELECT * FROM  FND_LOOKUP_VALUES

WHERE --LOOKUP_CODE LIKE 'AU%'AND

 LOOKUP_TYPE = 'VENDOR TYPE'

AND LANGUAGE = 'US'

; 

SELECT * FROM  GL_CODE_COMBINATIONS_V

where LAST_UPDATE_DATE like sysdate

ORDER BY LAST_UPDATE_DATE DESC

;

SELECT AP.*

 FROM AP_SUPPLIERS AP, AP_SUPPLIER_SITES_ALL APSA

WHERE AP.VENDOR_ID = APSA.VENDOR_ID

AND ORG_ID = 2400

AND APSA.CREATION_DATE LIKE SYSDATE;

  

  UPDATE XX_VENDOR_API_GL GL

  SET GL.VENDOR_ID = 

  (SELECT AP.VENDOR_ID FROM AP_SUPPLIERS AP

  WHERE AP.VENDOR_NAME = GL.VENDOR_NAME);

--------------------------------------------------------------------------------------------------------

declare

l_org_id number := 2400;

l_vendor_rec    ap_vendor_pub_pkg.r_vendor_rec_type;

l_return_status     VARCHAR2(20000);

l_msg_count     NUMBER;

l_msg_data  VARCHAR2(1000);

l_vendor_id NUMBER;

l_party_id  NUMBER;

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;

l_vendor_site_id    NUMBER;

l_party_site_id     NUMBER;

l_location_id   NUMBER;

V_count number:=0;

l_Vendor_No number;

l_liability number;

l_prepaid number;

l_term number;

v_country varchar2(100);

    l_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;

    l_vendor_contact_id NUMBER;

    l_per_party_id NUMBER;

    l_rel_party_id NUMBER;

    l_rel_id NUMBER;

    l_org_contact_id NUMBER;

    l_msg                  varchar2(200);

     

begin

for i in (

--run multiple times, if error appears or records dont move check us and other suppliers

select * 

 from 

XX_VENDOR_API_GL  where

-- VENDOR_NAME = '???? ????? ??????' AND 

-- NUM NOT IN (1,2,5)

VENDOR_ID IS NULL AND

  VENDOR_SITE_ID IS NULL

  

 ) loop

dbms_output.put_line(1);

begin

select code_combination_id

into l_liability

from GL_CODE_COMBINATIONS

where SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7=-- '01.000.00.2210000.0001.0000.0000';

i.LIABILITY_ACCOUNT;

exception

when no_data_found then null;

end;

dbms_output.put_line(2);

l_vendor_rec.VENDOR_TYPE_LOOKUP_CODE:='EMPLOYEES';--i.Vendor_Type;

--l_vendor_rec.segment1 := i.Vendor_No; 

l_vendor_rec.vendor_name := i.Vendor_Name;

l_vendor_rec.PAY_DATE_BASIS_LOOKUP_CODE:= 'DUE';

--l_vendor_rec.PAY_GROUP_LOOKUP_CODE:=i.PAY_GROUP_CODE;

l_vendor_rec.PAYMENT_CURRENCY_CODE := 'AED';

l_vendor_rec.invoice_CURRENCY_CODE := 'AED';

--l_vendor_rec.TERMS_NAME:=i.Pay_Terms;

l_vendor_rec.TERMS_ID:=10000;

--l_vendor_rec.ACCTS_PAY_CODE_COMBINATION_ID:=l_liability;

--l_vendor_rec.PREPAY_CODE_COMBINATION_ID:=l_prepaid; 

dbms_output.put_line('1--create vendor');

BEGIN

pos_vendor_pub_pkg.create_vendor

(

p_vendor_rec => l_vendor_rec,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

x_vendor_id => l_vendor_id,

x_party_id => l_party_id

);

IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)

  THEN     

    FOR i IN 1..FND_MSG_PUB.COUNT_MSG LOOP

      l_msg := FND_MSG_PUB.get( p_msg_index => i,

                                p_encoded   => FND_API.G_FALSE

                              );   

       dbms_output.put_line('The API call failed with error '||l_msg);

    END LOOP;

  ELSE

    dbms_output.put_line('The API call ended with SUCESSS status');

  END IF; 

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END; 

 

dbms_output.put_line('2--create vendor site');

select TERRITORY_CODE--, vl.*

into v_country

from FND_TERRITORIES_VL vl

where TERRITORY_SHORT_NAME  = 'United Arab Emirates'; --'China'

l_vendor_site_rec.vendor_id :=l_vendor_id;

l_vendor_site_rec.vendor_site_code :=i.Vendor_Site_Code;

l_vendor_site_rec.address_line1 := 'Fujairah';

l_vendor_site_rec.country := 'AE';

l_vendor_site_rec.city := i.City;

l_vendor_site_rec.org_id := l_org_id;--'121';

l_vendor_site_rec.purchasing_site_flag:='Y';

l_vendor_site_rec.pay_site_flag :='Y';

l_vendor_site_rec.rfq_only_site_flag :='N';

IF i.Country = 'United States' then

l_vendor_site_rec.state := '-' ;

l_vendor_site_rec.county := '-';

end if;

--l_vendor_site_rec.vat_code:=i.tax_Code;

l_vendor_site_rec.TERMS_ID:=10000;

l_vendor_site_rec.PAY_DATE_BASIS_LOOKUP_CODE:='DUE';

--l_vendor_site_rec.PAY_GROUP_LOOKUP_CODE:=i.PAY_GROUP_CODE;

l_vendor_site_rec.INVOICE_CURRENCY_CODE:=  'AED';

l_vendor_site_rec.PAYMENT_CURRENCY_CODE:=  'AED';

l_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID := l_liability;

--l_vendor_site_rec.PREPAY_CODE_COMBINATION_ID:=l_prepaid;  --AFZAL

--l_vendor_site_rec.PHONE:=i.CONTACT_NUMBER;

--l_vendor_site_rec.AUTO_TAX_CALC_FLAG:='Y';

 

BEGIN

pos_vendor_pub_pkg.create_vendor_site

(

p_vendor_site_rec => l_vendor_site_rec,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

x_vendor_site_id => l_vendor_site_id,

x_party_site_id => l_party_site_id,

x_location_id => l_location_id

);

IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)

  THEN     

    FOR i IN 1..FND_MSG_PUB.COUNT_MSG LOOP

      l_msg := FND_MSG_PUB.get( p_msg_index => i,

                                p_encoded   => FND_API.G_FALSE

                              );   

       dbms_output.put_line('The API call failed with error '||l_msg);

    END LOOP;

  ELSE

    dbms_output.put_line('The API call ended with SUCESSS status');

  END IF; 

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

dbms_output.put_line('vendor_site_id: '||l_vendor_site_id);

    dbms_output.put_line('return_status: '||l_return_status);

    dbms_output.put_line('msg_data: '||l_msg_data);

dbms_output.put_line(7);

if l_return_status = 'S' then

dbms_output.put_line(8);

UPDATE XX_VENDOR_API_GL SET status='Y',VENDOR_ID = l_vendor_id, VENDOR_SITE_ID = l_vendor_site_id

  where vendor_name||'.'=i.vendor_name||'.'

and VENDOR_SITE_CODE= i.Vendor_Site_Code;

if i.tax_Code like '%RCM%' then

update ZX_PARTY_TAX_PROFILE set PROCESS_FOR_APPLICABILITY_FLAG='N' ,ALLOW_OFFSET_TAX_FLAG='Y' where party_id=l_party_id;

--dbms_output.put_line(9);

dbms_output.put_line(i.tax_Code);

else

update ZX_PARTY_TAX_PROFILE set PROCESS_FOR_APPLICABILITY_FLAG='Y' ,ALLOW_OFFSET_TAX_FLAG='N' where party_id=l_party_id;

--dbms_output.put_line(10);

dbms_output.put_line(i.tax_Code);

end if;

else

        dbms_output.put_line( 'x_return_status: ' || l_return_status);

        dbms_output.put_line( 'x_msg_data: ' || l_msg_data);

end if;

COMMIT;

End loop;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line('ERROR :'||SQLERRM);

End;

 

  

Apps R12 to create Code Combination from Back End.

 

create or replace function create_ccid 

( p_concat_segs in varchar2

) return varchar2

is

  -- pragma autonomous_transaction; -- if you need autonomy!

  l_keyval_status     BOOLEAN;

  l_coa_id            NUMBER;

   l_err_msg          varchar2(2000);

   l_error varchar2(255);

begin

--SELECT * FROM  gl_sets_of_books;

  begin

    select chart_of_accounts_id

    into   l_coa_id

    from   gl_sets_of_books

    where  set_of_books_id = 2159;--fnd_profile.value('GL_SET_OF_BKS_ID');

  exception

    when no_data_found then

      dbms_output.put_line('Chart of Accounts ID not found from profile option GL_SET_OF_BKS_ID');

      dbms_output.put_line('Try setting up your environment with fnd_global.apps_initialize');

      raise;

  end;

  -- keyval_mode can be one of CREATE_COMBINATION CHECK_COMBINATION FIND_COMBINATION

  --create will only work if dynamic inserts on and cross validation rules not broken

  l_keyval_status := fnd_flex_keyval.validate_segs(

                                           'CREATE_COMBINATION',

                                           'SQLGL',

                                           'GL#',

                                           l_coa_id,

                                           p_concat_segs,

                                           'V',

                                           sysdate,

                                           'ALL', NULL, NULL, NULL, NULL,

                                           FALSE,FALSE, NULL, NULL, NULL);

                                

  if l_keyval_status then  

    return 'S';   

  else

   --return l_error;

    l_err_msg:=substr(fnd_flex_keyval.error_message, 1, 240);     --fnd_message.get;

    

    l_error := substr(fnd_flex_keyval.error_message, 1, 240); 

   dbms_output.put_line(l_error); 

   dbms_output.put_line('ERROR SEGMENT :');

   l_error := to_char(fnd_flex_keyval.error_segment);

   dbms_output.put_line(l_error); 

   dbms_output.put_line('ERROR ENCODED :');

   l_error := substr(fnd_flex_keyval.encoded_error_message, 1, 240);

   dbms_output.put_line(l_error); 

   dbms_output.put_line('FALSE'); 

    dbms_output.put_line(l_err_msg||substr(sqlerrm,150,3));

    return l_error;

  end if;

  EXCEPTION WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE(SQLERRM);

end create_ccid;

DECLARE

L_RETURN VARCHAR2(2000);

BEGIN

FOR I IN (SELECT LIABILITY_ACCOUNT FROM XX_VENDOR_API_GL) LOOP

SELECT create_ccid(i.LIABILITY_ACCOUNT)

INTO L_RETURN

 FROM DUAL;

COMMIT;

END LOOP;

END;