GL Cr Dr Memo Report

 GL Cr Dr Memo Report

SELECT dist_description, sum(dist_qty) dist_qty, sum(dist_local_amt) dist_local_amt, sum(dist_entered_amt) dist_entered_amt, trx_id, sum(local_amt_sum), sum(entered_amt_sum) FROM (

SELECT  fnd.description dist_description,

         '1' dist_qty,

         ROUND(((NVL(fnd.amount_cr,0)- NVL(fnd.amount_dr,0)) * gld.conversion_rate),2) dist_local_amt,

         NVL(fnd.amount_cr,0)- NVL(fnd.amount_dr,0) dist_entered_amt,

         fnd.trx_id,

        ROUND(((NVL(fnd.amount_cr,0)- NVL(fnd.amount_dr,0)) * gld.conversion_rate),2)  local_amt_sum,--replaced xxon_gl_drcrmemo_pkg code

       NVL(fnd.amount_cr,0)- NVL(fnd.amount_dr,0) entered_amt_sum

  FROM   FUN_DIST_LINES fnd,

         gl_daily_rates gld,

         fun_trx_headers fnh,

         fun_trx_batches fnb,

         gl_ledgers gll

 WHERE   fnd.party_type_flag = 'I'

         AND fnd.dist_type_flag = 'L'

         AND fnd.trx_id = fnh.trx_id

         AND fnh.batch_id = fnb.batch_id

         AND fnb.from_ledger_id = gll.ledger_id

         AND fnb.batch_date = gld.conversion_date

         AND gll.currency_code = gld.to_currency

         AND fnb.currency_code = gld.from_currency

         AND gld.conversion_type = 'Corporate'

UNION ALL

 SELECT  fnd.description dist_description,

         '1' dist_qty,

         ROUND(((NVL(fnd.amount_cr,0)- NVL(fnd.amount_dr,0)) * 1),2) dist_local_amt,

         NVL(fnd.amount_cr,0)- NVL(fnd.amount_dr,0) dist_entered_amt,

         fnd.trx_id,

         ROUND(((NVL(fnd.amount_cr,0)- NVL(fnd.amount_dr,0)) * 1),2) local_amt_sum,

       NVL(fnd.amount_cr,0)- NVL(fnd.amount_dr,0) entered_amt_sum

  FROM   FUN_DIST_LINES fnd,

         fun_trx_headers fnh,

         fun_trx_batches fnb,

         gl_ledgers gll

 WHERE   fnd.party_type_flag = 'I'

         AND fnd.dist_type_flag = 'L'

         AND fnd.trx_id = fnh.trx_id

         AND fnh.batch_id = fnb.batch_id

         AND fnb.from_ledger_id = gll.ledger_id

         AND gll.currency_code = fnb.currency_code  

) GROUP BY dist_description, trx_id, local_amt_sum, entered_amt_sum

***SELECT gl_currency_api.convert_amount (ftb.currency_code,  

                                       'USD',  

                                       ftb.gl_date,  

                                       ftb.exchange_rate_type,  

                                       Nvl(ftl.reci_amount_cr,ftl.reci_amount_dr) ) amount 

       , '1' qty

       , ftb.description description

       , ftl.trx_id

FROM fun_trx_lines ftl

    ,fun_trx_headers fth 

    ,fun_trx_batches ftb

WHERE ftb.batch_id = fth.batch_id

AND fth.trx_id = ftl.trx_id

****SELECT distinct fth.trx_id 

,gjh.doc_sequence_value document_number 

,ihzp.party_id  initiator_party_id

,ihzp.party_name initiator_party_name

,rhzp.party_id recipient_party_id

,rhzp.party_name recipient_party_name

,DECODE(SUBSTR(ihzp.party_name,1,3)

, '572', REPLACE(ixep.legal_entity_identifier,'-')

, '577','GST Reg No: '||ixep.legal_entity_identifier

, '579','GST Reg No: '||ixep.legal_entity_identifier

, '681','ABN: '||ixep.legal_entity_identifier 

, ixep.legal_entity_identifier) init_vat_reg_num 

--,DECODE(SUBSTR(rhzp.party_name,1,3),'572',REPLACE(rxep.legal_entity_identifier,'-'),rxep.legal_entity_identifier) recip_vat_reg_num 

, CASE 

    WHEN (SUBSTR(rhzp.party_name,1,3)='572')

        THEN REPLACE(rxep.legal_entity_identifier,'-')

    WHEN (SUBSTR(rhzp.party_name,1,3) IN ('577','579'))

        THEN 'GST Reg No: '||rxep.legal_entity_identifier

    WHEN (SUBSTR(rhzp.party_name,1,3) IN ('681'))

         THEN 'ABN: '||rxep.legal_entity_identifier  

    WHEN (     (upper(rxep.legal_entity_identifier) Not like '%VAT%') 

           AND (upper(rxep.legal_entity_identifier) Not like '%GST%')  

           AND (SUBSTR(rhzp.party_name,1,3) NOT IN ('572', '574', '577', '579', '681'))

        )

       THEN 'VAT: '||rxep.legal_entity_identifier 

   ELSE rxep.legal_entity_identifier

END recip_vat_reg_num 

,Case 

WHEN SUBSTR(ihzp.party_name,1,3) In (Select Fv.Flex_Value

                                     From Fnd_Flex_Value_Sets Fvs, Fnd_Flex_Values Fv

                                     Where Fvs.Flex_Value_Set_Name = 'XXON_LE_OU_LEVEL'

                                     And Fvs.Flex_Value_Set_Id = Fv.Flex_Value_Set_Id) 

     Then 'N'

Else 'Y'

END display_ou_code

,Case

When SUBSTR(rhzp.party_name,1,3) In (Select Fv.Flex_Value

                                       From Fnd_Flex_Value_Sets Fvs, Fnd_Flex_Values Fv

                                      Where Fvs.Flex_Value_Set_Name = 'XXON_LE_OU_LEVEL'

                                      And Fvs.Flex_Value_Set_Id = Fv.Flex_Value_Set_Id) 

     THEN 'N'     

Else 'Y'

END display_ou_code1

,Case

When SUBSTR(ihzp.party_name,1,3) In ('572', '577', '579', '681') 

     THEN 'TAX INVOICE'     

Else 'INVOICE'

END INV_TYPE 

,ihl.location_id initiator_location_id

,rhl.location_id recipient_location_id

,ihl.address_line_1  i_address_line_1

,ihl.address_line_2  i_address_line_2

,ihl.address_line_3  i_address_line_3

,ihl.town_or_city  i_town_or_city

,ihl.postal_code i_postal_code

,ift.territory_short_name i_country

,rhl.address_line_1  r_address_line_1

,rhl.address_line_2  r_address_line_2

,rhl.address_line_3  r_address_line_3

,rhl.town_or_city  r_town_or_city

,rhl.postal_code r_postal_code

,rft.territory_short_name r_country

,ftb.batch_number||'-'||fth.trx_number   transaction_number

,To_Char(ftb.gl_date,'DD-Mon-YY') curr_date

,ftb.note vat_para

,DECODE(SUBSTR(ihzp.party_name,1,3),'331','Company Identification No. 45193533, registerd ar Regional Court in Ostrava on 29th April 1992 under Rg B331', NULL) TAX_REG_TEXT

,(SELECT gll.currency_code FROM gl_ledgers gll WHERE  gll.ledger_id = ftb.from_ledger_id ) LOCAL_CURR

,ftb.currency_code  ENTERED_CURR

,(SELECT Sum(gl_currency_api.convert_amount (ftb.currency_code,  

                                       'USD',  

                                       ftb.gl_date,  

                                       ftb.exchange_rate_type,  

                                       Nvl(ftl.reci_amount_cr,ftl.reci_amount_dr) )) total_amount 

FROM fun_trx_lines ftl

WHERE  ftl.trx_id = fth.trx_id ) total_amount,

SUBSTR(ihzp.party_name,1,3) OU_CODE,

SUBSTR(rhzp.party_name,1,3) OU_CODE1

FROM fun_trx_batches ftb

   , fun_trx_headers fth

   , hz_parties ihzp

   , xle_entity_profiles ixep

   , hz_parties rhzp

   , xle_entity_profiles rxep

   , gl_je_headers gjh

   , gl_import_references gir

   , hr_all_organization_units haou1

   , hr_locations              ihl

   , hz_relationships          hzr_ou1

   , hr_operating_units        hou1

   , fnd_territories_vl ift

   , hr_all_organization_units haou2

   , hr_locations              rhl

   , hz_relationships          hzr_ou2

   , hr_operating_units        hou2

   , fnd_territories_vl rft

   WHERE instr(';'|| NVL(:p_batch_number, Ftb.Batch_Number) ||';', ';'|| Ftb.Batch_Number||';') > 0   -- Added Batch number : 18/11/2011

AND  ftb.batch_id = fth.batch_id

AND fth.initiator_id = ihzp.party_id

AND ihzp.status = 'A'

AND ftb.from_le_id = ixep.legal_entity_id

AND fth.recipient_id = rhzp.party_id

AND rhzp.status = 'A'

AND fth.to_le_id = rxep.legal_entity_id

and gjh.ledger_id = ftb.from_ledger_id

AND gjh.je_header_id = gir.je_header_id

and gjh.je_batch_id = gir.je_batch_id

AND gir.reference_2 = fth.batch_id   -- Batch Id

AND gir.reference_3 = fth.trx_id  -- Transaction_id

--AND gjh.Currency_conversion_type = 'Corporate'

AND gir.reference_1 = 'Intercompany Transaction' 

AND hzr_ou1.object_id(+)= ihzp.party_id

AND hzr_ou1.subject_table_name(+) = 'HR_ALL_ORGANIZATION_UNITS'

AND hzr_ou1.object_table_name(+)  = 'HZ_PARTIES'

AND hzr_ou1.relationship_type(+)  = 'INTERCOMPANY_OPERATING_UNIT'

AND hzr_ou1.relationship_code(+)  = 'OPERATING_UNIT_OF'

AND hzr_ou1.directional_flag(+)   = 'B'

AND hzr_ou1.status(+)             = 'A'

AND trunc(hzr_ou1.start_date(+))  <= trunc(sysdate)

AND trunc(nvl(hzr_ou1.end_date(+),sysdate)) >= trunc(sysdate)

AND hou1.organization_id(+)        = hzr_ou1.subject_id 

AND hou1.organization_id= haou1.organization_id 

AND haou1.location_id = ihl.location_id 

AND ihl.country = ift.territory_code (+)

AND hzr_ou2.object_id(+)= rhzp.party_id

AND hzr_ou2.subject_table_name(+) = 'HR_ALL_ORGANIZATION_UNITS'

AND hzr_ou2.object_table_name(+)  = 'HZ_PARTIES'

AND hzr_ou2.relationship_type(+)  = 'INTERCOMPANY_OPERATING_UNIT'

AND hzr_ou2.relationship_code(+)  = 'OPERATING_UNIT_OF'

AND hzr_ou2.directional_flag(+)   = 'B'

AND hzr_ou2.status(+)             = 'A'

AND trunc(hzr_ou2.start_date(+))  <= trunc(sysdate)

AND trunc(nvl(hzr_ou2.end_date(+),sysdate)) >= trunc(sysdate)

AND hou2.organization_id(+)        = hzr_ou2.subject_id 

AND hou2.organization_id= haou2.organization_id 

AND haou2.location_id = rhl.location_id 

AND rhl.country = rft.territory_code (+)

AND ftb.initiator_id = nvl(:p_initiator_id, ftb.initiator_id)

AND fth.recipient_id = nvl(:p_receiver_id, fth.recipient_id)

AND ihzp.party_id      = ftb.initiator_id 

AND rhzp.party_id(+)   = fth.recipient_id

--AND ihzp.party_id = nvl(:p_initiator_id, ihzp.party_id)

--AND rhzp.party_id = nvl(:p_receiver_id, rhzp.party_id) 

AND ftb.gl_date between nvl(:p_from_date, ftb.gl_date) and nvl(:p_to_date, ftb.gl_date)