Oracle Invoice Print Report Sql Query

 SELECT

trx.customer_trx_id || '_' || ps.payment_schedule_id SPLIT_KEY_ATTR ,    

trx.customer_trx_id     customer_trx_id,

( select 'Y' from dual

  where exists (Select 'X' from ra_customer_trx_lines_all lines

                                Where lines.billing_period_start_date is not null

                                 and lines.customer_trx_id = trx.customer_trx_id )) Billing_Period_Exist ,

(SELECT Count(DISTINCT(sales_order))

 FROM ra_customer_trx_lines_all lines 

 WHERE lines.customer_trx_id= trx.customer_trx_id ) total_Sales_Order,

(SELECT  lines.sales_order

FROM ra_customer_trx_lines_all lines

WHERE  lines.customer_trx_id  = trx.customer_trx_id

AND lines.sales_order IS NOT NULL

AND rownum =1

) Header_Sales_Order ,

(SELECT LISTAGG(DISTINCT wnd.PACKING_SLIP_NUMBER, ' , ') WITHIN GROUP (ORDER BY wnd.PACKING_SLIP_NUMBER) AS PACKING_SLIP_NUMBER

from ra_customer_trx_lines_all rctl,

     wsh_delivery_details wdd,

     wsh_delivery_assignments wda,

     wsh_new_deliveries wnd

where 1=1

and rctl.customer_trx_id  = trx.customer_trx_id

and rctl.sales_order = wdd.source_header_number

and wda.delivery_detail_id = wdd.delivery_detail_id

and wda.delivery_id = wnd.delivery_id

and rctl.interface_line_attribute7 = wnd.delivery_id

and wnd.PACKING_SLIP_NUMBER is not null)PACKING_SLIP_NUMBER,

nvl(ps.terms_sequence_number,1)  term_sequence_number,

trx.trx_number      trx_number,

to_char(trx.trx_date,'YYYY-MM-DD')        trx_date,

trx.invoice_currency_code  invoice_currency_code, 

t.name                  term_name, 

trx.waybill_number                      waybill_number,

        trx.ship_via                            ship_via,      

        to_char(trx.ship_date_actual,'YYYY-MM-DD')                    ship_date_actual,

        trx.purchase_order                      purchase_order_number,

        to_char(trx.purchase_order_date,'YYYY-MM-DD')                  purchase_order_date,

        to_char(ps.due_date,'YYYY-MM-DD')                             term_due_date_from_ps,

        b_bill.account_number                   bill_to_customer_number,

        b_bill_party.party_name   bill_to_customer_name,

         a_bill_loc.address1            bill_to_address1,

        a_bill_loc.address2            bill_to_address2,

        a_bill_loc.address3            bill_to_address3,

        a_bill_loc.address4            bill_to_address4,

        a_bill_loc.city                            bill_to_city,

        a_bill_loc.state            bill_to_state,

        a_bill_loc.province            bill_to_province,

        a_bill_loc.postal_code                     bill_to_postal_code,

        a_bill_loc.country                         bill_to_country,

    b_ship_party.party_name   ship_to_customer_name,       

    u_ship.party_site_id                   ship_to_site_number,

    a_ship_loc.address1            ship_to_address1,       

    a_ship_loc.address2            ship_to_address2,    

    a_ship_loc.address3            ship_to_address3,       

    a_ship_loc.address4            ship_to_address4,       

    a_ship_loc.city             ship_to_city,       

    a_ship_loc.postal_code             ship_to_postal_code,       

    a_ship_loc.country            ship_to_country,

      a_ship_loc.state            ship_to_state,

       a_ship_loc.province            ship_to_province,

     a_remit_loc.address1                       remit_to_address1,

        a_remit_loc.address2                       remit_to_address2,

        a_remit_loc.address3                       remit_to_address3,

        a_remit_loc.address4                       remit_to_address4,

        a_remit_loc.city                           remit_to_city,

        a_remit_loc.state                          remit_to_state,

        a_remit_loc.postal_code                    remit_to_postal_code,

        a_remit_loc.country                        remit_to_country ,

        u_bill.location bill_to_location,

        (SELECT  party.party_name

         FROM   JTF_RS_SALESREPS  sales,

         Hz_parties     party

         WHERE sales.RESOURCE_SALESREP_ID    =  trx.PRIMARY_RESOURCE_SALESREP_ID

         AND sales.RESOURCE_ID = party.party_id) primary_salesrep_name,  

                                 

(SELECT max(cp.URL)

FROM hz_contact_points       cp,

     hz_cust_account_roles   acct_role

  WHERE acct_role.cust_account_role_id = trx.bill_to_contact_id

    and acct_role.relationship_id = cp.relationship_id

    and cp.owner_table_name = 'HZ_PARTIES'

    and cp.contact_point_type='WEB'

    and cp.status ='A'

    and cp.primary_flag = 'Y') contact_web_url,  --Bug: 18705899

 

(SELECT max(cp.raw_phone_number)

FROM hz_contact_points       cp,

     hz_cust_account_roles   acct_role

  WHERE acct_role.cust_account_role_id = trx.bill_to_contact_id

    and acct_role.relationship_id = cp.relationship_id

    and cp.owner_table_name = 'HZ_PARTIES'

    and cp.contact_point_type='PHONE'

    and cp.phone_line_type='MOBILE'

    and cp.status ='A'

    and cp.primary_flag = 'Y') contact_mobile_ph_number,  --Bug: 18705899

 

 

(SELECT max(cp.email_address)

   FROM hz_contact_points       cp,

        hz_cust_account_roles   acct_role

  WHERE acct_role.cust_account_role_id = trx.bill_to_contact_id

    and acct_role.relationship_id = cp.relationship_id

    and cp.owner_table_name = 'HZ_PARTIES'

    and cp.contact_point_type='EMAIL'

    and cp.status ='A'

    and cp.primary_flag = 'Y') contact_email,  --Bug: 18705899

 

         (SELECT  party.PRIMARY_PHONE_NUMBER

         FROM   JTF_RS_SALESREPS  sales,

         Hz_parties     party

         WHERE sales.RESOURCE_SALESREP_ID    =  trx.PRIMARY_RESOURCE_SALESREP_ID

         AND sales.RESOURCE_ID = party.party_id) primary_salesrep_phone,       

                (SELECT  party.PRIMARY_PHONE_AREA_CODE

         FROM   JTF_RS_SALESREPS  sales,

         Hz_parties     party

         WHERE sales.RESOURCE_SALESREP_ID    =  trx.PRIMARY_RESOURCE_SALESREP_ID

         AND sales.RESOURCE_ID = party.party_id) salesrep_phone_areacode,

                (SELECT  party.PRIMARY_PHONE_COUNTRY_CODE

         FROM   JTF_RS_SALESREPS  sales,

         Hz_parties     party

         WHERE sales.RESOURCE_SALESREP_ID    =  trx.PRIMARY_RESOURCE_SALESREP_ID

         AND sales.RESOURCE_ID = party.party_id) salesrep_phone_countrycode,                   

    TO_CHAR(NVL( DECODE(types.accounting_affect_flag, 'Y', ps.amount_line_items_original, 'N',

  (SELECT SUM(extended_amount)

     FROM ra_customer_trx_lines_all lines

    WHERE lines.customer_trx_id = trx.customer_trx_id

  AND lines.line_type           ='LINE'

  ) ),to_number(0)),fnd_currency.get_format_mask(trx.invoice_currency_code, 40 ) ) line_amount,

  TO_CHAR(NVL( DECODE(types.accounting_affect_flag, 'Y', ps.tax_original, 'N',

  (SELECT SUM(extended_amount)

     FROM ra_customer_trx_lines_all lines

    WHERE lines.customer_trx_id = trx.customer_trx_id

  AND lines.line_type           ='TAX'

  ) ),to_number(0)),fnd_currency.get_format_mask(trx.invoice_currency_code, 40 ) ) tax_amount,

  TO_CHAR(NVL( DECODE(types.accounting_affect_flag, 'Y', ps.freight_original, 'N',

  (SELECT SUM(extended_amount)

     FROM ra_customer_trx_lines_all lines

    WHERE lines.customer_trx_id = trx.customer_trx_id

  AND lines.line_type           ='FREIGHT'

  ) ),to_number(0)),fnd_currency.get_format_mask(trx.invoice_currency_code, 40 ) ) freight_amount,

  TO_CHAR(NVL( DECODE(types.accounting_affect_flag, 'Y', ps.amount_due_original, 'N',

  (SELECT SUM(extended_amount)

     FROM ra_customer_trx_lines_all lines

    WHERE lines.customer_trx_id = trx.customer_trx_id

  ) ) + nvl(ps.amount_adjusted,to_number(0)) ,to_number(0)),fnd_currency.get_format_mask(trx.invoice_currency_code, 40 ) ) total_amount,

  (SELECT NVL(SUM(NVL(adj.receivables_charges_adjusted, 0)), 0)

                FROM   ar_adjustments_all adj

                WHERE  adj.customer_trx_id = trx.customer_trx_id

                  AND  adj.status = 'A'

                  AND  adj.receivables_trx_id <> -15) finance_charges,

AR_BPA_UTILS_PKG.fn_get_contact_name(trx.bill_to_contact_id) customer_contact_name,       

AR_BPA_UTILS_PKG.fn_get_phone(trx.bill_to_contact_id) customer_phone,

AR_BPA_UTILS_PKG.fn_get_fax(trx.bill_to_contact_id) customer_fax,

AR_BPA_UTILS_PKG.fn_get_header_level_so(trx.customer_trx_id) sales_order,

AR_BPA_UTILS_PKG.fn_trx_has_groups(trx.customer_trx_id) trx_has_groups,

AR_BPA_UTILS_PKG.fn_get_billing_line_level(trx.customer_trx_id) billing_line_level,

    HZ_FORMAT_PUB.format_address(a_bill_loc.location_id) formatted_bill_to_address,

    HZ_FORMAT_PUB.format_address(a_ship_loc.location_id) formatted_ship_to_address,

    HZ_FORMAT_PUB.format_address(a_remit_loc.location_id) formatted_remit_to_address,

    HZ_FORMAT_PUB.format_address(a_bill_loc.location_id,null,null,CHR(13)) formatted_bill_to_address1,

    HZ_FORMAT_PUB.format_address(a_ship_loc.location_id,null,null,CHR(13)) formatted_ship_to_address1,

    HZ_FORMAT_PUB.format_address(a_remit_loc.location_id,null,null,CHR(13)) formatted_remit_to_address1,

to_char(trunc(sysdate),'YYYY-MM-DD') current_date,

AR_BPA_UTILS_PKG.fn_get_header_level_co(trx.customer_trx_id) contract_number,

AR_BPA_UTILS_PKG.fn_get_profile_class_name(trx.customer_trx_id) profile_class_name,

trx.interface_header_context,

AR_BPA_UTILS_PKG.fn_get_tax_printing_option(trx.bill_to_site_use_id, trx.bill_to_customer_id) tax_printing_option,

trx.interface_header_attribute1,

trx.interface_header_attribute2,

trx.interface_header_attribute3,

trx.interface_header_attribute4,

trx.interface_header_attribute5,

trx.interface_header_attribute6,

trx.interface_header_attribute7,

trx.interface_header_attribute8,

trx.interface_header_attribute9,

trx.interface_header_attribute10,

trx.interface_header_attribute11,

trx.interface_header_attribute12,

trx.interface_header_attribute13,

trx.interface_header_attribute14,

trx.interface_header_attribute15,

a_ship_ps.PARTY_SITE_NUMBER  ship_to_location,

t_count.number_of_terms                 number_of_terms,

ps.terms_sequence_number        terms_sequence_number,

trx.org_id,

to_char(trunc(trx.creation_date),'YYYY-MM-DD')  creation_date,

trx.internal_notes,

to_char(nvl(ps.amount_due_original,to_number(0)) ,

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                        amount_due_original,

          to_char(nvl(ps.amount_applied,to_number(0)) ,

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                        amount_applied,

          nvl(ps.amount_due_remaining,to_number(0)) outstanding_balance,                       

          to_char(nvl(ps.amount_due_remaining,to_number(0)) ,

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                        amount_due_remaining,

          to_char(nvl(ps.amount_applied,to_number(0)) - nvl(ps.amount_credited,to_number(0)) +

          nvl(ps.discount_taken_earned,to_number(0)) + nvl(ps.discount_taken_unearned,to_number(0)) +

           nvl(ps.receivables_charges_charged,to_number(0)) ,   

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                                payments_and_credits,

          to_char(- nvl(ps.amount_applied,to_number(0)),   

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                                payments,

          to_char(nvl(ps.amount_credited,to_number(0)),   

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                                credits,

          to_char(nvl(ps.receivables_charges_charged,to_number(0)) ,

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                        amount_charges,

          to_char(nvl(ps.amount_adjusted,to_number(0)) ,

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                        amount_adjusted,

        ps.amount_due_original  total_amounts,

        ps.class trx_type,

        t.term_id,

        ps.payment_schedule_id,

        trx.comments,

        to_char(trunc(trx.START_DATE_COMMITMENT),'YYYY-MM-DD') START_DATE_COMMITMENT,

        to_char(trunc(trx.END_DATE_COMMITMENT),'YYYY-MM-DD') END_DATE_COMMITMENT,

        reason_lookup.meaning Credit_memo_reason,

        trx.previous_customer_trx_id,

        decode(trx.trx_class,'CM',

                                              (SELECT applied_trx.trx_number FROM   ra_customer_trx_all applied_trx  WHERE  applied_trx.customer_trx_id = trx.previous_customer_trx_id) ,

                                              'CB',

                                              (select ct.trx_number  from ra_customer_trx_all ct, ar_adjustments_all adj  where adj.chargeback_customer_trx_id  =  trx.customer_trx_id and ct.customer_trx_id = adj.customer_trx_id and trx.trx_class = 'CB'), null)   AS    previous_trx_number,

--FND_ACCESS_CONTROL_UTIL.Get_Org_Name(trx.org_id) org_name,

null org_name,

trx.legal_entity_id,

(select xle.name from  XLE_FIRSTPARTY_INFORMATION_V xle

where xle.legal_entity_id = trx.legal_entity_id) as legal_entity_name,

trx.ct_reference,

trx.cust_trx_type_seq_id,

types.name cust_trx_type_name,

trx.batch_source_seq_id,

batch.name batch_source_name,

trx.attribute1,

trx.attribute2,

trx.attribute3,

trx.attribute4,

trx.attribute5,

trx.attribute6,

trx.attribute7,

trx.attribute8,

trx.attribute9,

trx.attribute10,

trx.attribute11,

trx.attribute12,

trx.attribute13,

trx.attribute14,

trx.attribute15,

trx.SET_OF_BOOKS_ID,

trx.REASON_CODE,

trx.PURCHASE_ORDER_REVISION,

trx.CUSTOMER_REFERENCE,

to_char(trx.CUSTOMER_REFERENCE_DATE,'YYYY-MM-DD') ,

trx.EXCHANGE_RATE_TYPE,

to_char(trx.EXCHANGE_DATE,'YYYY-MM-DD'),

trx.EXCHANGE_RATE,

trx.ATTRIBUTE_CATEGORY,

trx.ORIG_SYSTEM_BATCH_NAME,

trx.FINANCE_CHARGES as Finance_Charge_Indicator,

trx.CREDIT_METHOD_FOR_RULES,

trx.CREDIT_METHOD_FOR_INSTALLMENTS,

trx.FOB_POINT,

trx.DEFAULT_USSGL_TRX_CODE_CONTEXT,

trx.DEFAULT_USSGL_TRANSACTION_CODE,

trx.RECURRED_FROM_TRX_NUMBER,

trx.STATUS_TRX,

trx.DOC_SEQUENCE_VALUE,

trx.GLOBAL_ATTRIBUTE1,

trx.GLOBAL_ATTRIBUTE2,

trx.GLOBAL_ATTRIBUTE3,

trx.GLOBAL_ATTRIBUTE4,

trx.GLOBAL_ATTRIBUTE5,

trx.GLOBAL_ATTRIBUTE6,

trx.GLOBAL_ATTRIBUTE7,

trx.GLOBAL_ATTRIBUTE8,

trx.GLOBAL_ATTRIBUTE9,

trx.GLOBAL_ATTRIBUTE10,

trx.GLOBAL_ATTRIBUTE11,

trx.GLOBAL_ATTRIBUTE12,

trx.GLOBAL_ATTRIBUTE13,

trx.GLOBAL_ATTRIBUTE14,

trx.GLOBAL_ATTRIBUTE15,

trx.GLOBAL_ATTRIBUTE16,

trx.GLOBAL_ATTRIBUTE17,

trx.GLOBAL_ATTRIBUTE18,

trx.GLOBAL_ATTRIBUTE19,

trx.GLOBAL_ATTRIBUTE20,

trx.GLOBAL_ATTRIBUTE_CATEGORY,

trx.EDI_PROCESSED_FLAG,

trx.EDI_PROCESSED_STATUS,

trx.MRC_EXCHANGE_RATE_TYPE,

trx.MRC_EXCHANGE_DATE,

trx.MRC_EXCHANGE_RATE,

trx.PAYMENT_SERVER_ORDER_NUM,

trx.APPROVAL_CODE,

trx.ADDRESS_VERIFICATION_CODE,

ps.NUMBER_OF_DUE_DATES,

ps.STATUS,

to_char(ps.GL_DATE_CLOSED,'YYYY-MM-DD'),

to_char(ps.ACTUAL_DATE_CLOSED,'YYYY-MM-DD'),

ps.AMOUNT_LINE_ITEMS_REMAINING,

ps.AMOUNT_IN_DISPUTE,

ps.AMOUNT_CREDITED,

ps.RECEIVABLES_CHARGES_REMAINING,

ps.FREIGHT_REMAINING,

ps.TAX_REMAINING,

ps.DISCOUNT_TAKEN_EARNED,

ps.DISCOUNT_TAKEN_UNEARNED,

ps.ACCTD_AMOUNT_DUE_REMAINING,

trx.SPECIAL_INSTRUCTIONS,

a_bill_loc.county                         bill_to_county,

a_ship_loc.county                         ship_to_county,

a_remit_loc.county                         remit_to_county,

(SELECT territory_short_name FROM fnd_territories_vl WHERE territory_code=a_bill_loc.country)

AS bill_to_country_name,

(SELECT territory_short_name FROM fnd_territories_vl WHERE territory_code=a_ship_loc.country)

AS ship_to_country_name,

rep_registration_number   cust_tax_regn_no,

(CASE

WHEN exists

(SELECT 1

  FROM

  ra_customer_trx_lines_all lines,

         ZX_RATES_B       rates,

         ZX_REPORTING_TYPES_B  reporting_types,       

         ZX_REPORT_CODES_ASSOC assoc

   WHERE

          customer_trx_id=trx.customer_trx_id

          and lines.vat_tax_id=rates.tax_rate_id                                       

          AND reporting_types.tax_regime_code = rates.tax_regime_code      

          AND  assoc.entity_id   =  rates.tax_rate_id

          AND  assoc.entity_code = 'ZX_RATES'  

          and reporting_types.reporting_type_code = 'REVERSE_CHARGE_VAT'

          and assoc.reporting_type_id = reporting_types.reporting_type_id

)        

THEN 'Y'

ELSE 'N'

END ) reverse_charge_vat_invoice,

AR_BPA_UTILS_PKG.fn_get_contact_name_party(trx.ship_to_party_contact_id) ship_to_contact_name,       

AR_BPA_UTILS_PKG.fn_get_phone_party(trx.ship_to_party_contact_id) ship_to_phone,

AR_BPA_UTILS_PKG.fn_get_fax_party(trx.ship_to_party_contact_id) ship_to_fax ,

(SELECT 

  to_char(sum( NVL( adj.line_adjusted, 0) + NVL( adj.tax_adjusted, 0)  )

  ,fnd_currency.get_format_mask(trx.invoice_currency_code,40))

 

        FROM ar_adjustments_all adj

WHERE  PS.PAYMENT_SCHEDULE_ID =ADJ.PAYMENT_SCHEDULE_ID

AND      adj.status = 'A'

AND     adj.receivables_trx_id  <> -15  ) AS   line_tax_adjustment  ,     

(SELECT 

  to_char(sum( NVL( adj.freight_adjusted, 0) )

  ,fnd_currency.get_format_mask(trx.invoice_currency_code,40))

 

        FROM ar_adjustments_all adj

WHERE  PS.PAYMENT_SCHEDULE_ID =ADJ.PAYMENT_SCHEDULE_ID

AND      adj.status = 'A'

AND     adj.receivables_trx_id  <> -15  ) AS   freight_adjustment ,

trx.STRUCTURED_PAYMENT_REFERENCE    

 

FROM

        ar_invoice_count_terms_v                t_count,

        ar_payment_schedules_all                ps,

        ra_terms_lines                          tl,

        ra_terms                                t,

 

        ra_cust_trx_types_all                   types,

        ra_batch_sources_all                    batch,

        ra_customer_trx_all                     trx,

        hz_cust_accounts                        b_bill,

        hz_parties                              b_bill_party,

        hz_cust_acct_sites_all                  a_bill,

        hz_party_sites                          a_bill_ps,

        hz_locations                            a_bill_loc,

 

        hz_parties                              b_ship_party,       

 

        hz_party_sites                          a_ship_ps,

        hz_locations                            a_ship_loc,

 

        ar_remit_to_locs_all                    a_remit,

        hz_locations                            a_remit_loc,       

        hz_cust_site_uses_all                   u_bill,

        hz_party_site_uses                      u_ship,

        ar_lookups                              reason_lookup,

        zx_party_tax_profilE                    tax

        &P_DYNAMIC_FROM_CLAUSE       

 

WHERE   &P_DYNAMIC_WHERE_CLAUSE

        trx.cust_trx_type_seq_id            = types.cust_trx_type_seq_id

 

        AND trx.batch_source_seq_id                    = batch.batch_source_seq_id

 

        AND trx.term_id                         = tl.term_id(+)

        AND trx.term_id                         = t.term_id(+)

        AND trx.customer_trx_id =  PS.CUSTOMER_TRX_ID

       

        /*AND NVL(ps.terms_sequence_number,

            NVL(tl.sequence_num,0))             = NVL(tl.sequence_num,

                                                  NVL(ps.terms_sequence_number,0))*/

           AND reason_lookup.lookup_type(+) = 'CREDIT_MEMO_REASON'

           AND reason_lookup.lookup_code(+) = trx.reason_code            

        AND nvl(trx.term_id, -1)                = t_count.term_id

        AND trx.bill_to_customer_id             = b_bill.cust_account_id

        ANd b_bill.party_id                     = b_bill_party.party_id

        AND trx.ship_to_party_id             = b_ship_party.party_id(+)

 

        AND trx.bill_to_site_use_id             = u_bill.site_use_id

 

        AND trx.ship_to_party_site_use_id       = u_ship.party_site_use_id(+)

     

        AND u_bill.cust_acct_site_id            = a_bill.cust_acct_site_id(+)

 

        AND a_bill.party_site_id                = a_bill_ps.party_site_id(+)

        AND a_bill_loc.location_id(+)           = a_bill_ps.location_id

        AND u_ship.party_site_id            = a_ship_ps.party_site_id(+)

 

        AND a_ship_loc.location_id(+)           = a_ship_ps.location_id             

        AND trx.remit_to_address_seq_id         = a_remit.address_loc_seq_id(+)

        AND a_remit.location_id                 = a_remit_loc.location_id(+)

 

        AND tax.party_id(+)                     = b_bill_party.party_id

        AND tax.party_type_code(+)              = 'THIRD_PARTY'

 

UNION

 

SELECT     

trx.customer_trx_id || '_' ||  ps.payment_schedule_id SPLIT_KEY_ATTR ,

trx.customer_trx_id     customer_trx_id,

( select 'Y' from dual

  where exists (Select 'X' from ra_customer_trx_lines_all lines

                                Where lines.billing_period_start_date is not null

                                and lines.customer_trx_id = trx.customer_trx_id )) Billing_Period_Exist ,

(SELECT Count(DISTINCT(sales_order))

 FROM ra_customer_trx_lines_all lines 

 WHERE lines.customer_trx_id= trx.customer_trx_id ) total_Sales_Order,

(SELECT  lines.sales_order

FROM ra_customer_trx_lines_all lines

WHERE  lines.customer_trx_id  = trx.customer_trx_id

AND lines.sales_order IS NOT NULL

AND rownum =1

) Header_Sales_Order    ,

(SELECT LISTAGG(DISTINCT wnd.PACKING_SLIP_NUMBER, ' , ') WITHIN GROUP (ORDER BY wnd.PACKING_SLIP_NUMBER) AS PACKING_SLIP_NUMBER

from ra_customer_trx_lines_all rctl,

     wsh_delivery_details wdd,

     wsh_delivery_assignments wda,

     wsh_new_deliveries wnd

where 1=1

and rctl.customer_trx_id  = trx.customer_trx_id

and rctl.sales_order = wdd.source_header_number

and wda.delivery_detail_id = wdd.delivery_detail_id

and wda.delivery_id = wnd.delivery_id

and rctl.interface_line_attribute7 = wnd.delivery_id

and wnd.PACKING_SLIP_NUMBER is not null)PACKING_SLIP_NUMBER,

1  term_sequence_number,

trx.trx_number      trx_number,

to_char(trx.trx_date,'YYYY-MM-DD')        trx_date,

trx.invoice_currency_code  invoice_currency_code, 

t.name                  term_name, 

trx.waybill_number                      waybill_number,

        trx.ship_via                            ship_via,      

        to_char(trx.ship_date_actual,'YYYY-MM-DD')                    ship_date_actual,

        trx.purchase_order                      purchase_order_number,

        to_char(trx.purchase_order_date,'YYYY-MM-DD')                 purchase_order_date,

        to_char(ps.due_date,'YYYY-MM-DD')                             term_due_date_from_ps,

        b_bill.account_number                   bill_to_customer_number,

        b_bill_party.party_name   bill_to_customer_name,

         a_bill_loc.address1            bill_to_address1,

        a_bill_loc.address2            bill_to_address2,

        a_bill_loc.address3            bill_to_address3,

        a_bill_loc.address4            bill_to_address4,

        a_bill_loc.city                            bill_to_city,

        a_bill_loc.state            bill_to_state,

        a_bill_loc.province            bill_to_province,

        a_bill_loc.postal_code                     bill_to_postal_code,

        a_bill_loc.country                         bill_to_country,

    b_ship_party.party_name   ship_to_customer_name,       

    u_ship.party_site_id                   ship_to_site_number,

    a_ship_loc.address1            ship_to_address1,       

    a_ship_loc.address2            ship_to_address2,    

    a_ship_loc.address3            ship_to_address3,       

    a_ship_loc.address4            ship_to_address4,       

    a_ship_loc.city             ship_to_city,       

    a_ship_loc.postal_code             ship_to_postal_code,       

    a_ship_loc.country            ship_to_country,

      a_ship_loc.state            ship_to_state,

       a_ship_loc.province            ship_to_province,

     a_remit_loc.address1                       remit_to_address1,

        a_remit_loc.address2                       remit_to_address2,

        a_remit_loc.address3                       remit_to_address3,

        a_remit_loc.address4                       remit_to_address4,

        a_remit_loc.city                           remit_to_city,

        a_remit_loc.state                          remit_to_state,

        a_remit_loc.postal_code                    remit_to_postal_code,

        a_remit_loc.country                        remit_to_country ,

        u_bill.location bill_to_location,

        (SELECT  party.party_name

         FROM   JTF_RS_SALESREPS  sales,

         Hz_parties     party

         WHERE sales.RESOURCE_SALESREP_ID    =  trx.PRIMARY_RESOURCE_SALESREP_ID

         AND sales.RESOURCE_ID = party.party_id) primary_salesrep_name,  

 

(SELECT max(cp.URL)

FROM hz_contact_points       cp,

     hz_cust_account_roles   acct_role

  WHERE acct_role.cust_account_role_id = trx.bill_to_contact_id

    and acct_role.relationship_id = cp.relationship_id

    and cp.owner_table_name = 'HZ_PARTIES'

    and cp.contact_point_type='WEB'

    and cp.status ='A'

    and cp.primary_flag = 'Y') contact_web_url,  --Bug: 18705899

 

(SELECT max(cp.raw_phone_number)

FROM hz_contact_points       cp,

     hz_cust_account_roles   acct_role

  WHERE acct_role.cust_account_role_id = trx.bill_to_contact_id

    and acct_role.relationship_id = cp.relationship_id

    and cp.owner_table_name = 'HZ_PARTIES'

    and cp.contact_point_type='PHONE'

    and cp.phone_line_type='MOBILE'

    and cp.status ='A'

    and cp.primary_flag = 'Y') contact_mobile_ph_number,  --Bug: 18705899

 

 

(SELECT max(cp.email_address)

   FROM hz_contact_points       cp,

        hz_cust_account_roles   acct_role

  WHERE acct_role.cust_account_role_id = trx.bill_to_contact_id

    and acct_role.relationship_id = cp.relationship_id

    and cp.owner_table_name = 'HZ_PARTIES'

    and cp.contact_point_type='EMAIL'

    and cp.status ='A'

    and cp.primary_flag = 'Y') contact_email,  --Bug: 18705899

 

         (SELECT  party.PRIMARY_PHONE_NUMBER

         FROM   JTF_RS_SALESREPS  sales,

         Hz_parties     party

         WHERE sales.RESOURCE_SALESREP_ID    =  trx.PRIMARY_RESOURCE_SALESREP_ID

         AND sales.RESOURCE_ID = party.party_id) primary_salesrep_phone,                               

                (SELECT  party.PRIMARY_PHONE_AREA_CODE

         FROM   JTF_RS_SALESREPS  sales,

         Hz_parties     party

         WHERE sales.RESOURCE_SALESREP_ID    =  trx.PRIMARY_RESOURCE_SALESREP_ID

         AND sales.RESOURCE_ID = party.party_id) salesrep_phone_areacode,

                (SELECT  party.PRIMARY_PHONE_COUNTRY_CODE

         FROM   JTF_RS_SALESREPS  sales,

         Hz_parties     party

         WHERE sales.RESOURCE_SALESREP_ID    =  trx.PRIMARY_RESOURCE_SALESREP_ID

         AND sales.RESOURCE_ID = party.party_id) salesrep_phone_countrycode,                   

    TO_CHAR(NVL( DECODE(types.accounting_affect_flag, 'Y', ps.amount_line_items_original, 'N',

  (SELECT SUM(extended_amount)

     FROM ra_customer_trx_lines_all lines

    WHERE lines.customer_trx_id = trx.customer_trx_id

  AND lines.line_type           ='LINE'

  ) ),to_number(0)),fnd_currency.get_format_mask(trx.invoice_currency_code, 40 ) ) line_amount,

  TO_CHAR(NVL( DECODE(types.accounting_affect_flag, 'Y', ps.tax_original, 'N',

  (SELECT SUM(extended_amount)

     FROM ra_customer_trx_lines_all lines

    WHERE lines.customer_trx_id = trx.customer_trx_id

  AND lines.line_type           ='TAX'

  ) ),to_number(0)),fnd_currency.get_format_mask(trx.invoice_currency_code, 40 ) ) tax_amount,

  TO_CHAR(NVL( DECODE(types.accounting_affect_flag, 'Y', ps.freight_original, 'N',

  (SELECT SUM(extended_amount)

     FROM ra_customer_trx_lines_all lines

    WHERE lines.customer_trx_id = trx.customer_trx_id

  AND lines.line_type           ='FREIGHT'

  ) ),to_number(0)),fnd_currency.get_format_mask(trx.invoice_currency_code, 40 ) ) freight_amount,

  TO_CHAR(NVL( DECODE(types.accounting_affect_flag, 'Y', ps.amount_due_original, 'N',

  (SELECT SUM(extended_amount)

     FROM ra_customer_trx_lines_all lines

    WHERE lines.customer_trx_id = trx.customer_trx_id

  ) ) + nvl(ps.amount_adjusted,to_number(0)),to_number(0)),fnd_currency.get_format_mask(trx.invoice_currency_code, 40 ) ) total_amount,

  (SELECT NVL(SUM(NVL(adj.receivables_charges_adjusted, 0)), 0)

                FROM   ar_adjustments_all adj

                WHERE  adj.customer_trx_id = trx.customer_trx_id

                  AND  adj.status = 'A'

                  AND  adj.receivables_trx_id <> -15) finance_charges,

AR_BPA_UTILS_PKG.fn_get_contact_name(trx.bill_to_contact_id) customer_contact_name,       

AR_BPA_UTILS_PKG.fn_get_phone(trx.bill_to_contact_id) customer_phone,

AR_BPA_UTILS_PKG.fn_get_fax(trx.bill_to_contact_id) customer_fax,

AR_BPA_UTILS_PKG.fn_get_header_level_so(trx.customer_trx_id) sales_order,

AR_BPA_UTILS_PKG.fn_trx_has_groups(trx.customer_trx_id) trx_has_groups,

AR_BPA_UTILS_PKG.fn_get_billing_line_level(trx.customer_trx_id) billing_line_level,

    HZ_FORMAT_PUB.format_address(a_bill_loc.location_id) formatted_bill_to_address,

    HZ_FORMAT_PUB.format_address(a_ship_loc.location_id) formatted_ship_to_address,

    HZ_FORMAT_PUB.format_address(a_remit_loc.location_id) formatted_remit_to_address,

    HZ_FORMAT_PUB.format_address(a_bill_loc.location_id,null,null,CHR(13)) formatted_bill_to_address1,

    HZ_FORMAT_PUB.format_address(a_ship_loc.location_id,null,null,CHR(13)) formatted_ship_to_address1,

    HZ_FORMAT_PUB.format_address(a_remit_loc.location_id,null,null,CHR(13)) formatted_remit_to_address1,

to_char(trunc(sysdate),'YYYY-MM-DD') current_date,

AR_BPA_UTILS_PKG.fn_get_header_level_co(trx.customer_trx_id) contract_number,

AR_BPA_UTILS_PKG.fn_get_profile_class_name(trx.customer_trx_id) profile_class_name,

trx.interface_header_context,

AR_BPA_UTILS_PKG.fn_get_tax_printing_option(trx.bill_to_site_use_id, trx.bill_to_customer_id) tax_printing_option,

trx.interface_header_attribute1,

trx.interface_header_attribute2,

trx.interface_header_attribute3,

trx.interface_header_attribute4,

trx.interface_header_attribute5,

trx.interface_header_attribute6,

trx.interface_header_attribute7,

trx.interface_header_attribute8,

trx.interface_header_attribute9,

trx.interface_header_attribute10,

trx.interface_header_attribute11,

trx.interface_header_attribute12,

trx.interface_header_attribute13,

trx.interface_header_attribute14,

trx.interface_header_attribute15,

a_ship_ps.PARTY_SITE_NUMBER ship_to_location,

t_count.number_of_terms                 number_of_terms,

ps.terms_sequence_number        terms_sequence_number,

trx.org_id,

to_char(trunc(trx.creation_date),'YYYY-MM-DD') creation_date,

trx.internal_notes,

to_char(nvl(ps.amount_due_original,to_number(0)) ,

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                        amount_due_original,

          to_char(nvl(ps.amount_applied,to_number(0)) ,

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                        amount_applied,

          nvl(ps.amount_due_remaining,to_number(0)) outstanding_balance,                       

          to_char(nvl(ps.amount_due_remaining,to_number(0)) ,

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                        amount_due_remaining,

          to_char(nvl(ps.amount_applied,to_number(0)) - nvl(ps.amount_credited,to_number(0)) +

          nvl(ps.discount_taken_earned,to_number(0)) + nvl(ps.discount_taken_unearned,to_number(0)) +

           nvl(ps.receivables_charges_charged,to_number(0)) ,   

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                                payments_and_credits,

          to_char(- nvl(ps.amount_applied,to_number(0)),   

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                                payments,

          to_char(nvl(ps.amount_credited,to_number(0)),   

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                                credits,

          to_char(nvl(ps.receivables_charges_charged,to_number(0)) ,

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                        amount_charges,

          to_char(nvl(ps.amount_adjusted,to_number(0)) ,

                fnd_currency.get_format_mask(trx.invoice_currency_code,40))

                        amount_adjusted,

        ps.amount_due_original  total_amounts,

        ps.class trx_type,

        t.term_id,

        ps.payment_schedule_id,

        trx.comments,

        to_char(trunc(trx.START_DATE_COMMITMENT),'YYYY-MM-DD') START_DATE_COMMITMENT,

        to_char(trunc(trx.END_DATE_COMMITMENT),'YYYY-MM-DD') END_DATE_COMMITMENT,

        reason_lookup.meaning Credit_memo_reason,

        trx.previous_customer_trx_id,

        decode(trx.trx_class,'CM',

                                              (SELECT applied_trx.trx_number FROM   ra_customer_trx_all applied_trx WHERE  applied_trx.customer_trx_id = trx.previous_customer_trx_id) ,

                                              'CB',

                                               (select ct.trx_number  from ra_customer_trx_all ct, ar_adjustments_all adj  where adj.chargeback_customer_trx_id  =  trx.customer_trx_id and ct.customer_trx_id = adj.customer_trx_id and trx.trx_class = 'CB'),null)   AS    previous_trx_number,

--FND_ACCESS_CONTROL_UTIL.Get_Org_Name(trx.org_id) org_name,

null org_name,

trx.legal_entity_id,

(select xle.name from  XLE_FIRSTPARTY_INFORMATION_V xle

where xle.legal_entity_id = trx.legal_entity_id) as legal_entity_name,

trx.ct_reference,

trx.cust_trx_type_seq_id,

types.name cust_trx_type_name,

trx.batch_source_seq_id,

batch.name batch_source_name,

trx.attribute1,

trx.attribute2,

trx.attribute3,

trx.attribute4,

trx.attribute5,

trx.attribute6,

trx.attribute7,

trx.attribute8,

trx.attribute9,

trx.attribute10,

trx.attribute11,

trx.attribute12,

trx.attribute13,

trx.attribute14,

trx.attribute15,

trx.SET_OF_BOOKS_ID,

trx.REASON_CODE,

trx.PURCHASE_ORDER_REVISION,

trx.CUSTOMER_REFERENCE,

to_char(trx.CUSTOMER_REFERENCE_DATE,'YYYY-MM-DD'),

trx.EXCHANGE_RATE_TYPE,

to_char(trx.EXCHANGE_DATE,'YYYY-MM-DD'),

trx.EXCHANGE_RATE,

trx.ATTRIBUTE_CATEGORY,

trx.ORIG_SYSTEM_BATCH_NAME,

trx.FINANCE_CHARGES as Finance_Charge_Indicator,

trx.CREDIT_METHOD_FOR_RULES,

trx.CREDIT_METHOD_FOR_INSTALLMENTS,

trx.FOB_POINT,

trx.DEFAULT_USSGL_TRX_CODE_CONTEXT,

trx.DEFAULT_USSGL_TRANSACTION_CODE,

trx.RECURRED_FROM_TRX_NUMBER,

trx.STATUS_TRX,

trx.DOC_SEQUENCE_VALUE,

trx.GLOBAL_ATTRIBUTE1,

trx.GLOBAL_ATTRIBUTE2,

trx.GLOBAL_ATTRIBUTE3,

trx.GLOBAL_ATTRIBUTE4,

trx.GLOBAL_ATTRIBUTE5,

trx.GLOBAL_ATTRIBUTE6,

trx.GLOBAL_ATTRIBUTE7,

trx.GLOBAL_ATTRIBUTE8,

trx.GLOBAL_ATTRIBUTE9,

trx.GLOBAL_ATTRIBUTE10,

trx.GLOBAL_ATTRIBUTE11,

trx.GLOBAL_ATTRIBUTE12,

trx.GLOBAL_ATTRIBUTE13,

trx.GLOBAL_ATTRIBUTE14,

trx.GLOBAL_ATTRIBUTE15,

trx.GLOBAL_ATTRIBUTE16,

trx.GLOBAL_ATTRIBUTE17,

trx.GLOBAL_ATTRIBUTE18,

trx.GLOBAL_ATTRIBUTE19,

trx.GLOBAL_ATTRIBUTE20,

trx.GLOBAL_ATTRIBUTE_CATEGORY,

trx.EDI_PROCESSED_FLAG,

trx.EDI_PROCESSED_STATUS,

trx.MRC_EXCHANGE_RATE_TYPE,

trx.MRC_EXCHANGE_DATE,

trx.MRC_EXCHANGE_RATE,

trx.PAYMENT_SERVER_ORDER_NUM,

trx.APPROVAL_CODE,

trx.ADDRESS_VERIFICATION_CODE,

ps.NUMBER_OF_DUE_DATES,

ps.STATUS,

to_char(ps.GL_DATE_CLOSED,'YYYY-MM-DD'),

to_char(ps.ACTUAL_DATE_CLOSED,'YYYY-MM-DD'),

ps.AMOUNT_LINE_ITEMS_REMAINING,

ps.AMOUNT_IN_DISPUTE,

ps.AMOUNT_CREDITED,

ps.RECEIVABLES_CHARGES_REMAINING,

ps.FREIGHT_REMAINING,

ps.TAX_REMAINING,

ps.DISCOUNT_TAKEN_EARNED,

ps.DISCOUNT_TAKEN_UNEARNED,

ps.ACCTD_AMOUNT_DUE_REMAINING,

trx.SPECIAL_INSTRUCTIONS,

a_bill_loc.county                         bill_to_county,

a_ship_loc.county                         ship_to_county,

a_remit_loc.county                         remit_to_county,

(SELECT territory_short_name FROM fnd_territories_vl WHERE territory_code=a_bill_loc.country)

AS bill_to_country_name,

(SELECT territory_short_name FROM fnd_territories_vl WHERE territory_code=a_ship_loc.country)

AS ship_to_country_name,

rep_registration_number   cust_tax_regn_no,

(CASE

WHEN exists

(SELECT 1

  FROM

  ra_customer_trx_lines_all lines,

         ZX_RATES_B       rates,

         ZX_REPORTING_TYPES_B  reporting_types,       

         ZX_REPORT_CODES_ASSOC assoc

   WHERE

          customer_trx_id=trx.customer_trx_id

          and lines.vat_tax_id=rates.tax_rate_id                                      

          AND reporting_types.tax_regime_code = rates.tax_regime_code      

          AND  assoc.entity_id   =  rates.tax_rate_id

          AND  assoc.entity_code = 'ZX_RATES'  

          and reporting_types.reporting_type_code = 'REVERSE_CHARGE_VAT'

          and assoc.reporting_type_id = reporting_types.reporting_type_id

)        

THEN 'Y'

ELSE 'N'

END ) reverse_charge_vat_invoice ,

AR_BPA_UTILS_PKG.fn_get_contact_name_party(trx.ship_to_party_contact_id) ship_to_contact_name,       

AR_BPA_UTILS_PKG.fn_get_phone_party(trx.ship_to_party_contact_id) ship_to_phone,

AR_BPA_UTILS_PKG.fn_get_fax_party(trx.ship_to_party_contact_id) ship_to_fax ,

to_char(to_number(0) ,fnd_currency.get_format_mask(trx.invoice_currency_code,40))

AS   line_tax_adjustment  ,      

to_char(to_number(0) ,fnd_currency.get_format_mask(trx.invoice_currency_code,40))

AS   freight_adjustment ,

trx.STRUCTURED_PAYMENT_REFERENCE    

 

FROM

        ar_invoice_count_terms_v                t_count,

        ar_payment_schedules_all                ps,

        ra_terms                                t,

 

        ra_cust_trx_types_all                   types,

        ra_batch_sources_all                    batch,

        ra_customer_trx_all                     trx,

        hz_cust_accounts                        b_bill,

        hz_parties                              b_bill_party,

        hz_cust_acct_sites_all                  a_bill,

        hz_party_sites                          a_bill_ps,

        hz_locations                            a_bill_loc,

 

        hz_parties                              b_ship_party,       

 

        hz_party_sites                          a_ship_ps,

        hz_locations                            a_ship_loc,

 

        ar_remit_to_locs_all                    a_remit,

        hz_locations                            a_remit_loc,       

        hz_cust_site_uses_all                   u_bill,

        hz_party_site_uses                   u_ship,

        ar_lookups                              reason_lookup,

        zx_party_tax_profilE                    tax

        &P_DYNAMIC_FROM_CLAUSE       

 

WHERE   &P_DYNAMIC_WHERE_CLAUSE1

        trx.cust_trx_type_seq_id                = types.cust_trx_type_seq_id

        AND trx.batch_source_seq_id             = batch.batch_source_seq_id

        AND trx.term_id                         = t.term_id(+)

        AND reason_lookup.lookup_type(+)        = 'CREDIT_MEMO_REASON'

        AND reason_lookup.lookup_code(+)        = trx.reason_code            

        AND nvl(trx.term_id, -1)                = t_count.term_id

        AND trx.bill_to_customer_id             = b_bill.cust_account_id

        ANd b_bill.party_id                     = b_bill_party.party_id

        AND trx.ship_to_party_id                = b_ship_party.party_id(+)

        AND trx.bill_to_site_use_id             = u_bill.site_use_id

        AND trx.ship_to_party_site_use_id       = u_ship.party_site_use_id(+)

        AND u_bill.cust_acct_site_id            = a_bill.cust_acct_site_id(+)

        AND a_bill.party_site_id                = a_bill_ps.party_site_id(+)

        AND a_bill_loc.location_id(+)           = a_bill_ps.location_id

       

        AND u_ship.party_site_id                = a_ship_ps.party_site_id(+)  

        AND a_ship_loc.location_id(+)           = a_ship_ps.location_id             

        AND trx.remit_to_address_seq_id         = a_remit.address_loc_seq_id(+)

        AND a_remit.location_id                 = a_remit_loc.location_id(+)

        AND tax.party_id(+)                     = b_bill_party.party_id

        AND tax.party_type_code(+)              = 'THIRD_PARTY'

                &P_DYNAMIC_ORDER_BY_CLAUSE

Query for Supplier Payment Comparison of that Period(Vendor Spend Query)

 SELECT POS.SEGMENT1

       , HP.PARTY_NAME

  ,cur_period.PAYMENT_AMOUNT  cur_pay_amt

  ,prev_period.PAYMENT_AMOUNT  prev_pay_amt

  ,ROUND((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100),1)||'%' increase_decrease

FROM

        (SELECT    AIA.VENDOR_ID              VENDOR_ID

          ,SUM(NVL(aipa.AMOUNT,0))    PAYMENT_AMOUNT

        FROM  AP_INVOICES_ALL AIA,

         ap_invoice_payments_all       aipa,

         gl_periods                    gl

        WHERE 1=1

        AND   AIA.INVOICE_ID                = AIPA.INVOICE_ID

        AND   aipa.period_name              = gl.period_name

        AND gl.period_set_name ='XX Period Name' 

AND gl.PERIOD_YEAR = :P_Fiscal_Year

AND gl.PERIOD_NAME = :P_Period

--AND   SYSDATE BETWEEN gl.start_date and gl.end_date

GROUP BY AIA.VENDOR_ID

        ) cur_period,

        (SELECT     AIA.VENDOR_ID     VENDOR_ID  

          ,SUM(NVL(aipa.AMOUNT,0))    PAYMENT_AMOUNT

        FROM  AP_INVOICES_ALL AIA,

         ap_invoice_payments_all       aipa,

         gl_periods                    gl

        WHERE 1=1

        AND   AIA.INVOICE_ID                = AIPA.INVOICE_ID

        AND   aipa.period_name              = gl.period_name

        AND gl.period_set_name ='XX Period Name'

AND gl.period_name = (

                      SELECT  gl.period_name

                              FROM  

                               gl_periods      gl,

                       gl_periods      gl2

                              WHERE 1=1

                      AND gl.period_set_name ='XX Period Name'

                      AND (

       ( 

           gl2.period_name = :P_Comp_Period

  AND gl2.PERIOD_YEAR = :P_Comp_Fis_Year

  AND trunc(gl2.start_date) between gl.start_date and gl.end_date

    )

  OR

  (

  ( trunc(gl2.start_date-1) between gl.start_date and gl.end_date ) 

   AND gl2.period_name = :P_Period

   AND gl2.PERIOD_YEAR = :P_Fiscal_Year

   AND :P_Comp_Period IS NULL

   AND :P_Comp_Fis_Year IS NULL

  )

  )

                      AND gl2.period_set_name ='XX Period Name'

  )   

        GROUP BY AIA.VENDOR_ID

        ) prev_period

        ,POZ_SUPPLIERS POS

        ,HZ_PARTIES     HP

WHERE POS.VENDOR_ID = cur_period.vendor_id(+)

AND   POS.VENDOR_ID  = Prev_period.vendor_id(+)

AND   POS.party_id  = hp.party_id

AND ((COALESCE(NULL, :P_Supplier) IS NULL)

       OR (HP.PARTY_NAME IN (:P_Supplier)))

and (NVL(cur_period.PAYMENT_AMOUNT ,0)<>0 OR NVl(prev_period.PAYMENT_AMOUNT ,0)<>0)

AND abs((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100)) > = NVL(:P_Change,abs((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100)))

link between PO and Projects Query in oracle fusion (po_headers_all and pjf_projects_all_b)

 SELECT

ph.segment1 CommitmentNumber
,pl.line_num ItemNumber
,(SELECT DISTINCT esi.item_number
  FROM egp_system_items_b esi
  WHERE esi.inventory_item_id=pl.item_id) ITEM
,pl.item_description ItemDescription
,plt.line_type ITEM_TYPE
,gcc.segment1||gcc.segment2||gcc.segment3 ACCT_UNIT
,gcc.segment4 ACCT_CATEGORY
,ppa.segment1 ActivityNumber
,pl.list_price ENT_UNIT_CST
,pll.quantity QUANTITY
,(pll.quantity*pl.list_price) CommitmentItemAmount
,pll.need_by_date POSTED_DATE
,ps.segment1 CompanyNumber
,hp.party_name Description
,ppa.attribute3 ProjectIdentifier
,ph.creation_date CommitmentDate
,ppn.full_name BUYER_CODE
,pld.location_code REQ_LOCATION
,pl.attribute1||','||pl.attribute2 BudgetLineItem
,'Purchase Order' CommitmentType
FROM po_headers_all ph,po_lines_all pl
,po_line_types_tl plt
,gl_code_combinations gcc
,po_distributions_all pd
,pjf_projects_all_b ppa
,po_line_locations_all pll
,poz_suppliers ps
,hz_parties hp
,per_person_names_f ppn
,per_location_details_f_tl pld
,per_location_details_f pldf
WHERE ph.po_header_id=pl.po_header_id
AND plt.line_type_id=pl.line_type_id
AND plt.language=USERENV('LANG')
AND pd.po_header_id=pl.po_header_id
AND pd.po_line_id=pl.po_line_id
AND pd.code_combination_id=gcc.code_combination_id
AND gcc.enabled_flag='Y'
AND SYSDATE BETWEEN NVL(gcc.start_date_active,SYSDATE) AND NVL(gcc.end_date_active,SYSDATE)
AND pd.Pjc_Project_id=ppa.project_id
AND pll.po_header_id=pl.po_header_id
AND pll.po_line_id=pl.po_line_id
AND ph.vendor_id=ps.vendor_id
AND ps.party_id=hp.party_id
AND ppn.person_id=ph.agent_id
AND ppn.name_type='GLOBAL'
AND SYSDATE BETWEEN NVL(ppn.effective_start_date,SYSDATE) AND NVL(ppn.effective_end_date,SYSDATE)
AND ps.enabled_flag='Y'
AND SYSDATE BETWEEN NVL(ps.start_date_active,SYSDATE) AND NVL(ps.end_date_active,SYSDATE)
AND pldf.location_id=pd.Deliver_to_location_ID
AND pldf.active_status='A'
AND SYSDATE BETWEEN NVL(pldf.effective_start_date,SYSDATE) AND NVL(pldf.effective_end_date,SYSDATE)
AND pld.location_details_id=pldf.location_details_id
AND pld.language=USERENV('LANG')

Generate Certificate from Oracle Fusion Erp Cloud

 Step 1: click Instance url

https://<Instance url>/FndManageImportExportFilesService?wsdl

Step 2: 

You'll see certificate data in this tag : dsig:X509Certificate

Step 3: 

Your certificate should be in this format : 

-----BEGIN CERTIFICATE-----

Copy data from Step 2

-----END CERTIFICATE-----

open a notepad and copy those header and footer and insert data in between

Step 4 :

Save the file as <Filename.cer>

Employee with all element entries Query in Oracle HCM Cloud

 SELECT 

       ldg.name                         ldg_name,

       pect1.classification_name        primary_classification,

       (SELECT pect2.classification_name

       FROM pay_ele_classifications_tl pect2

       WHERE pect2.classification_id          = pet.secondary_classification_id

       AND pect2.LANGUAGE                     = USERENV('LANG')

       )                                secondary_classification,

       pldf.attribute1                  region,

       ftl.territory_short_name         country_name,

       ppslv.person_number              person_number,

       ppnf.full_name                   employee_name,

       pldft.location_name              location,

       houft.name                       department,

       (SELECT ffvv.description

        FROM gl_code_combinations   gcc,

             fnd_flex_values_vl     ffvv,

             fnd_id_flex_segments   fifs

        WHERE gcc.code_combination_id   = paam.default_code_comb_id

        AND gcc.segment5                = ffvv.flex_value

        AND ffvv.flex_value_set_id      = fifs.flex_value_set_id

        AND fifs.SEGMENT_NAME           = 'Team'

        AND fifs.id_flex_code           = 'GL#'

       ) team,

       pet.reporting_name                  reporting_name,

       to_char(pee.creation_date, 'MM-DD-YYYY') entry_date,

       to_char(pee.effective_start_date, 'MM-DD-YYYY') effective_start_date,

       to_char(pee.effective_end_date, 'MM-DD-YYYY') effective_end_date,

       pet.element_name                    element_name,

       pet.base_element_name               element_code,

       pet.description                     element_description,

       DECODE(pet.processing_type, 'R', 'Yes', 'N', 'No', 'No') recurring_entry,

       ldg.default_currency_code           currency,

       peevf.screen_entry_value            amount,

       ( CASE

WHEN ldg.default_currency_code = 'USD'

THEN to_number(peevf.screen_entry_value)

            ELSE

            ROUND((to_number(peevf.screen_entry_value)) * gdr.conversion_rate,2)

         END

) amount_usd,

       pgft.name                        job_grade,

       pjf.job_code                     job_code,

       pelf.attribute1                  bonus_eligible,

       pelf.attribute2                  merit_eligible,

       pelf.attribute3                  pension_eligible

  FROM pay_legislative_data_groups      ldg,

       pay_element_types_vl             pet,

   (select gdr1.conversion_rate, gdr1.from_currency

FROM gl_daily_rates gdr1

WHERE  gdr1.to_currency = 'USD'

AND gdr1.conversion_type = 'Corporate'

AND gdr1.conversion_date = (SELECT MAX(gdr2.conversion_date)

FROM  gl_daily_rates gdr2

WHERE gdr2.to_currency = 'USD'

AND gdr2.conversion_type = 'Corporate'

AND gdr2.from_currency=gdr1.from_currency

AND gdr2.conversion_date <= sysdate)

)                    gdr,

       pay_element_links_f              pelf,

       pay_ele_classifications_tl       pect1,

       pay_element_entries_f            pee,

       pay_element_entry_values_f       peevf,

       pay_input_values_f               piv,

       per_person_names_f               ppnf,

       per_person_secured_list_v        ppslv,

       per_all_assignments_m            paam,

       hr_organization_units_f_tl       houft,

       per_location_details_f           pldf,

       per_location_details_f_tl        pldft,

       per_grades_f_tl                  pgft,

       per_jobs_f                       pjf,

       per_locations                    pl,

       fnd_territories_tl               ftl

WHERE (( coalesce(NULL, :p_ldg_name) IS NULL )

          OR ( ldg.name IN (:p_ldg_name) ) )

   AND ( ( coalesce(NULL, :p_reporting_name) IS NULL )

          OR ( pet.reporting_name IN ( :p_reporting_name ) ) )

   AND ( ( coalesce(NULL, :p_element_name) IS NULL )

          OR ( pet.element_type_id IN ( :p_element_name ) ) )

   AND ( ( coalesce(NULL, :p_processing_type) IS NULL )

          OR ( pet.processing_type IN ( :p_processing_type ) ) )

   AND ldg.legislative_data_group_id  = pet.legislative_data_group_id

   AND ldg.default_currency_code      = gdr.from_currency(+)

   AND pet.element_type_id            = pee.element_type_id

   AND pet.element_type_id            = pelf.element_type_id

   AND pet.classification_id          = pect1.classification_id

   AND UPPER(pet.element_name) NOT LIKE '%SALARY%'

   AND pect1.LANGUAGE                 = USERENV('LANG')

   AND TRUNC(SYSDATE) BETWEEN pet.effective_start_date AND pet.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN pelf.effective_start_date AND pelf.effective_end_date

   AND pee.person_id                  = ppnf.person_id

   AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

   AND pee.element_entry_id           = peevf.element_entry_id

   AND pee.last_update_date           = peevf.last_update_date

   AND pee.effective_start_date       = peevf.effective_start_date

   AND peevf.input_value_id           = piv.input_value_id

   AND piv.element_type_id            = pet.element_type_id

   AND piv.base_name                  = 'Amount'

   AND TRUNC(SYSDATE) BETWEEN piv.effective_start_date AND piv.effective_end_date

   AND ppnf.person_id                 = ppslv.person_id

   AND TRUNC(SYSDATE) BETWEEN ppslv.effective_start_date AND ppslv.effective_end_date

   AND ppnf.person_id                 = paam.person_id

   AND ppnf.name_type                 = 'GLOBAL'

   AND paam.organization_id           = houft.organization_id

   AND houft.LANGUAGE                 = USERENV('LANG')

   AND paam.effective_latest_change   = 'Y'

   AND paam.assignment_type           = 'E'

   AND paam.assignment_status_type    = 'ACTIVE'

   AND paam.primary_assignment_flag   = 'Y'

   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

   AND paam.location_id               = pldf.location_id

   AND pldf.location_details_id       = pldft.location_details_id

   AND pldft.LANGUAGE                 = USERENV('LANG')

   AND paam.grade_id                  = pgft.grade_id

   AND pgft.LANGUAGE                  = USERENV('LANG')

   AND TRUNC(SYSDATE) BETWEEN pgft.effective_start_date AND pgft.effective_end_date

   AND paam.job_id                    = pjf.job_id

   AND TRUNC(SYSDATE) BETWEEN pjf.effective_start_date AND pjf.effective_end_date

   AND paam.location_id               = pl.location_id

   AND pl.country                     = ftl.TERRITORY_CODE

   AND ftl.LANGUAGE                   = USERENV('LANG')

   AND (( coalesce(NULL, :p_full_name) IS NULL )

         OR ( ppnf.full_name IN (:p_full_name) ) )

   AND pee.effective_start_date <= :p_end_date

   AND pee.effective_end_date >= :p_start_date 

   --AND :p_start_date <= :P_end_date

ORDER BY ldg.name, ppnf.full_name, pet.element_name,pee.effective_start_date DESC

Payroll Element Configuration Query in HCM Oracle Cloud

 WITH pay_retro AS

(

SELECT rule_value, pet1.element_name

          FROM pay_tmplt_rule_values ptrv, 

               pay_tmplt_rules_f ptrf ,

               pay_element_types_vl pet1, 

               pay_templates pt

         WHERE ptrv.template_id = pt.template_id 

           AND pt.base_name = pet1.element_name

           AND ptrf.rule_id = ptrv.rule_id

           AND ptrf.rule_name = 'Retro'

),

pay_proration AS

(

SELECT rule_value, pet2.element_name

          FROM pay_tmplt_rule_values ptrv, 

               pay_tmplt_rules_f ptrf ,

               pay_element_types_vl pet2, 

               pay_templates pt

         WHERE ptrv.template_id = pt.template_id 

           AND pt.base_name = pet2.element_name

           AND ptrf.rule_id = ptrv.rule_id

           AND ptrf.rule_name = 'Proration'

)

SELECT ldg.name legislative_data_group

      ,pec.classification_name   primary_classification

      ,pec1.classification_name  secondary_classification

      ,NVL ((SELECT territory_short_name

      FROM fnd_territories_vl

      WHERE (territory_code = SUBSTR(pet.element_name,4,2))

     ),

(SELECT territory_short_name

      FROM fnd_territories_vl

      WHERE (territory_code = ldg.legislation_code))) country_name

      ,pet.element_name          name

      ,pet.reporting_name

      ,pet.description     

      ,TO_CHAR(pef.effective_start_date,'MM/DD/YYYY') effective_date

      ,TO_CHAR(pef.effective_end_date,'MM/DD/YYYY')   effective_end_date

      ,pef.input_currency_code   input_currency

      ,ptd1.definition_name      earliest_entry_date

      ,ptd2.definition_name      latest_entry_date

      ,DECODE(pet.use_at_asg_level, 'Y', 'Assignment level',

              DECODE(pet.use_at_term_level, 'Y', 'Term level', 'Pay relationship level')

             ) employment_level

      ,DECODE(hrl_petproc.meaning, 'Recurring', 'Yes', 'Nonrecurring', 'No') recurring

      ,pef.multiple_entries_allowed_flag multiple_entries_allowed

      ,pp.rule_value proration

      ,pr.rule_value retroactive

      ,pelf.element_link_name  element_eligibility_name

      ,pelf.standard_link_flag automatic_entry 

      , CASE WHEN :p_effective_start_date >= pef.effective_start_date AND :p_effective_start_date < pef.effective_end_date  

             THEN 'ACTIVE'

             ELSE 'INACTIVE'

             END AS            status

      ,pelf.attribute2         merit_eligible

      ,pelf.attribute1         bonus_eligible

      ,pelf.attribute3         pension_eligible

  FROM pay_element_types_vl       pet

      ,pay_ele_classifications_vl pec

      ,pay_ele_classifications_vl pec1

      ,pay_element_links_f        pelf

      ,pay_element_types_f        pef

      ,per_legislative_data_groups_vl ldg                     

      ,hr_lookups                 hrl_petproc

      ,pay_time_definitions       ptd1

      ,pay_time_definitions       ptd2

  ,pay_retro                  pr

  ,pay_proration              pp

 WHERE 1=1

   AND pet.classification_id           = pec.classification_id

   AND pet.secondary_classification_id = pec1.classification_id(+)

   AND pet.element_type_id             = pelf.element_type_id(+)

   AND pet.element_type_id             = pef.element_type_id

   AND ldg.legislative_data_group_id   = pet.legislative_data_group_id

   AND UPPER(pet.reporting_name) not like '%SALARY%'                

   AND pet.processing_type             = hrl_petproc.lookup_code

   AND hrl_petproc.lookup_type LIKE 'PAY_PROCESSING_TYPE'

   AND TRUNC(SYSDATE) BETWEEN hrl_petproc.start_date_active AND hrl_petproc.end_date_active

   AND pet.starting_time_def_id = ptd1.time_definition_id

   AND pet.ending_time_def_id   = ptd2.time_definition_id

   AND pet.element_name                = pp.element_name(+)

   AND pet.element_name                = pr.element_name(+)

   AND ((COALESCE(NULL, :param_ldg_name) IS NULL)

       OR (ldg.NAME IN (:param_ldg_name)))

   AND ((COALESCE(NULL, :param_element_name) IS NULL)

       OR (pet.element_type_id IN (:param_element_name)))

   AND ((COALESCE(NULL, :param_rep_name) IS NULL)                 

       OR (pet.reporting_name IN (:param_rep_name)))              

   AND (CASE WHEN :param_status = 'All'

             THEN 'All'

             WHEN :p_effective_start_date >= pef.effective_start_date AND :p_effective_start_date < pef.effective_end_date  

             THEN 'ACTIVE'

             ELSE 'INACTIVE'

         END)  = :param_status

   ORDER BY ldg.legislation_code, pet.element_name

Query to get DFF and Segment Values

 SELECT ffv.descriptive_flexfield_name “DFF Name”,

ffv.application_table_name “Table Name”,
ffv.title “Title”,
ap.application_name “Application”,
ffc.descriptive_flex_context_code “Context Code”,
ffc.descriptive_flex_context_name “Context Name”,
ffc.description “Context Desc”,
ffc.enabled_flag “Context Enable Flag”,
att.column_seq_num “Segment Number”,
att.form_left_prompt “Segment Name”,
att.application_column_name “Column”,
fvs.flex_value_set_name “Value Set”,
att.display_flag “Displayed”,
att.enabled_flag “Enabled”,
att.required_flag “Required”

FROM apps.fnd_descriptive_flexs_vl ffv,
apps.fnd_descr_flex_contexts_vl ffc,
apps.fnd_descr_flex_col_usage_vl att,
apps.fnd_flex_value_sets fvs,
apps.fnd_application_vl ap

WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id=ffv.application_id
AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name
AND ffv.application_id = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id=att.flex_value_set_id
AND ffv.title like ‘Give Title Name’
AND ffc.descriptive_flex_context_code like ‘Give Context Code Value’

ORDER BY att.column_seq_num

Query to get AP Payments detail in Oracle

Overview: This query is use to get AP modules payment details with following columns such as invoice id, invoice number, voucher number, payment method, payment reference, payment date, amount, payment status, bank name in Oracle 

SELECT      aia.invoice_id,

             aia.invoice_num,

             aia.DOC_SEQUENCE_VALUE  Voucher_num,

             aca.payment_method_code,

             aca.check_number payment_reference,

             aca.check_date payment_date,

             aca.amount,

             aca.status_lookup_code payment_status,

             cbv.bank_name,

             aip.attribute1 UTR_NO

      FROM   ap_invoices_all aia,

             ap_invoice_payments_all aip,

             ap_checks_all aca,

             ce_bank_acct_uses_all cbau,

             ce_bank_accounts cba,

             ce_banks_v cbv

     WHERE   aia.invoice_id = aip.invoice_id

             AND aca.check_id = aip.check_id

             AND cbau.bank_acct_use_id = aca.ce_bank_acct_use_id

             AND cbau.bank_account_id = cba.bank_account_id

             AND cba.bank_id = cbv.bank_party_id

Sub-Template in Oracle Fusion

For creating the Sub Template in Oracle Fusion follow below steps:

Step 1: Login to Oracle Fusion Instance.

Step 2: Navigate to Catalog page using the Navigation, Navigator — Reports and Analytics — Browse Catalog.

Step 3: Create the Data Model. For creating the Data Model follow bellow steps,

Step 3A: On catalog page Go To — New — Data Model.

Step 3B: On Data Model screen create the new Data Set by clicking on the Plus icon(+).

Step 3C: Give the Name for the Data Set and select the respective data source.

Step 3D: Write the query in the query block and click on OK button. You can use below query.

SELECT

a.po_header_id Po_header,

b.po_header_id,

b.po_line_id,

b.item_id,

b.unit_price,

b.quantity,

b.base_unit_price

FROM

po_headers_all a,

po_lines_all b

WHERE

a.po_header_id = b.po_header_id

AND a.segment1 = 162352

Step 3F: Now save the Data Model. You can save the Data Model to My Folder or Shared Folder anywhere as per your requirement.

Step 4: Now we have to create the Layout, for that follow bellow steps,

Step 4A: For Creating the layout you can click on Create Layout button which is on the Data Model page or you can go by using navigation New — Report from the catalog page.

Step 4B: In top left corner we have option to upload the Data Model. Upload the Data Model there by browsing it.

Step 4C: Now click on Generate RTF layout based on selected Data Model Button.

Step 4D: After this step the RTF fill will get created automatically.

Step 4E: Now save the Layout at the same location where we had save the Data Model.

Step 5: We had done with the report creation now we have to create the sub-template, for that follow below steps.

Step 5A: We will call the logo from sub template on main template for that create the sub template with below format.

 < ?Template:ORACLE? >

 {LOGO_IMAGE}

 < ?end template? >

 

4. Upload the subtemplate to oracle fusion instance.

5. Download Main template on local Machine and call the subtemplate.

 Syntax to import the sub-template:

  <?import:xdoxsl:///path_of_sub_template/sub_template_name.xsb?>

 E.g: <?import:xdoxsl:///Custom/Test/SubTemplate.xsb? > 

 Syntax to call the template:

  <?call-template:template_name?> 

  Eg: <?call-template:ORACLE?> 

6. Upload the Main Template in Oracle Fusion Instance.

7. Run the report and validate the output.

Most Important Oracle Fusion Technical Interview Questions

What are the Reporting tools available in the Oracle Fusion?

BIP Reports

OTBI reports

Smart View Reports

Financial Reporting Studio

****************************************************************

What is OTBI Report?

OTBI is the report tool available in the Oracle Fusion, Which is used to create the reports in oracle cloud. OTBI is the very user friendly tool available in oracle fusion, where we can develop the reports with just drag and drop options. It does not require the SLQ expertise and the Fusion tables knowledge to build the reports. This is the very interesting tool available in Oracle Fusion to extract the data from the system.

 

What is FR Studio in Oracle Fusion?

Financial Reporting Studio is a client based Financial Reporting tool which is uses to build the Financial reports in Oracle Fusion. This is more like FSG reports in Oracle Apps. This FR studio works on the GL balances. This Uses drag and drop functionality to create a grid to design the rows, columns

and pages of the financial report. This Contains grids and other objects that are reusable across multiple reports. FR Studio Uses the GL Balances Cube dimensions on either rows, columns, pages,or Point of Views (POV).

What is Smart View Report?

Smart View is Excel Add In based Reporting Tool Suitable for End users comfortable with Excel spreadsheets. This Provides the Multidimensional View of data. Smart View Only provided the GL data.

Smart View is: Microsoft Excel Based Reporting Optionally, available for upload to Financial Reporting Center and then download to your local drive for use.

Balance sheets using a familiar spreadsheet environment.

Smart View has two main features: -

Ad hoc Analysis: Enables you to interactively analyses balances.

Financial Reports: Define reports like income statements and

balance sheets using a familiar spreadsheet environment.

 

What is BIP Report?

BIP is the Business Intelligence Publisher reporting tool, which is uses to develop the Complex reports in Oracle Cloud. This is specially uses to develop the Statuary reports where we need to build the specific layouts. This is more technical tool need the SQL expertise and the Technical knowledge of the Cloud Application too.

***************************************************************

Which are the Triggers available in Oracle Fusion BIP reports?

But in Fusion BIP reports , Oracle has only given two report triggers that is :

before Data

After Data

*************************************************************

How to get the Oracle Fusion application information’s in the BIP reports?

In Oracle Apps R12 reports , we can easily get the user information through Profile option like USER_ID, RESP_ID but in Oracle Fusion to get the user information , we need to use fusion system variables as below

System Variables                    Variables Description

xdo_user_name

User ID of the user submitting the report. Like Which fusion. User running this report

xdo_user_roles

Roles assigned to the user submitting the report. Like from Which role this report is running.

xdo_user_report_oracle_lang

Report language from the user's account preferences.

xdo_user_report_locale

Report locale from the user's account preferences.

xdo_user_ui_oracle_lang

User interface language from the user's account preferences.

xdo_user_ui_locale

User interface locale from the user's account preferences.

For Example : Select  :xdo_user_name

                          :xdo_user_roles

                       From Dual

*******************************************************************

What is My Folder and Shared Folder in Fusion Reports?

My Folders

'My Folders' is your own folder. It means it is specific to each user. when you will create your report under this folder then no one in the application can see and access these reports because these are saved and created under your My folder. so this is totally yours personal Development.

Shared Folders

Shared Folders is your Application Common Folder. It means it is shared across users of the application as per the roles. Shared folders have many Sub-folders related to the Work related and Module Related.

 

Oracle has already given standard OTBI reports, Data Models and Dashboards under the sub-folders in the Shared Folders. When you will create any report under the sub-folder of Shared Folders then your report will be visible and accessible to other users of the application as per the Permission and role of that user.

****************************************************************

What is Sandbox in Oracle Fusion?

In EBS Sandbox related to Server but in Oracle fusion Sandbox is totally different concept.

In Oracle Fusion , We will Use Sandbox to do any kind of Personalizations or Extensions or Page layout changes in Oracle Fusion Web-pages. It means we need to create Sandbox first to make any changes in Oracle Fusion application in terms of Personalizations or Extensions. Then all the changes will be done under this sandbox and this is the important feature of the sandbox that , we can do the changes in the application without impacting the complete application so these changes will only apply under the sandbox and other users of the application will not see these changes until unless you have tested the changes under sandbox and publish this sandbox. Once you will publish the sandbox in fusion then all the changes under the sandbox will be published to all the users in the application.

****************************************************************

What is Schedule Process in Oracle Fusion?

Schedule Process in Oracle Fusion is same as Concurrent Requests in Oracle apps r12. We run the ESS jobs as a Schedule Processes in Oracle Fusion. ESS job is same as Concurrent Program in Oracle Apps r12.ESS job is the concept of Oracle Fusion with the help of this, we can run our Reports, Packages and Scripts in Oracle Fusion applications. We will first have learnt What is ESS job in Oracle Fusion and then Oracle ESS job definition.  ESS jobs in fusion apps is the same as Concurrent Program in Oracle apps r12. As we use concurrent Program to run the Reports, Procedures and Scripts in Oracle apps in the same way we use ESS jobs to run the BIP reports, Procedures and Scripts. We register Reports, scripts in fusion apps as a ESS jobs and user runs these ESS jobs as a Scheduled Processes in Oracle Fusion same as Concurrent Requests in Oracle apps R12. To create the ESS jobs, We first need to Create the Oracle ESS job definition then after that we can run the BIP reports, Procedures and Scripts through ESS jobs in Fusion Schedules Processes.

**************************************************************

How many Types of Roles Available in Oracle Fusion?

Oracle has provided these four Types of roles.

Abstract role

Job roles

Duty roles

Data roles

*************************************************************

What is Job Set in Oracle Fusion?

Job set is same like Request set in Oracle apps. Like in Oracle apps , We combine multiple concurrent Programs in a Request set in the same way , We Combine Multiple ESS jobs in a Fusion Job set. Job set is the set of Multiple ESS Jobs. When We want to run the same set of ESS jobs Together in Oracle Cloud, then we can add these ESS jobs in the job set and then We only need to run this concurrent Job set and all the ESS jobs under this Job set can be run together in sequentially or Parallelly order.

*************************************************************

Fusion Tables for the ESS jobs?

There are two Oracle fusion ess tables.

 FUSION.ESS_REQUEST_HISTORY

 FUSION.ESS_REQUEST_PROPERTY

***********************************************************

What Is ESS job in Oracle Fusion?

ESS job is same like concurrent Program in Oracle apps. If we need to run the BIP reports in the Fusion Application then we need to register these reports as a ESS jobs in Oracle Fusion. Then after that we can run this report in the Fusion as a Schedule process.

***********************************************************

What is UCM in Oracle Fusion?

UCM is the Universal Content Management Server Available in Oracle Cloud. UCM is very important in Oracle Cloud SAAS Environment. If We need to do Inbound Integration or We are doing the Outbound Integration, UCM plays very important roles in that. In Cloud UCM is same like FTP server in Oracle Apps r12. Where we put the Inbound Files to process through by Oracle Concurrent Requests and to Generate the outbound files too which will be picked by some other system. In this Way, Oracle Cloud has already created different spaces in the UCM for different works. We Just need to Identify that space in UCM for our relevant work. Inside UCM, there are different Locations  and each have different paths. We cannot create new locations or Path in the UCM. We need to use the existing locations under the UCM.

*********************************************************

Functioning of UCM in Oracle Cloud

If we are using the UCM for Data Migrations with the Help of FBDI in Oracle Cloud then First we need to identify the right location as per our Data Entity. For Example, if We are migrating the Supplier Master then we need to identify the location of UCM server where we need to put the Supplier Master File and from where Cloud Program read this File.

What is BPM in Oracle Fusion?

BPM means Business Process Management. BPM is a complete set of tools for creating, executing, and optimizing business processes in Oracle Fusion. We can design the approval processes in BPM. We can design the Business approval processes in Oracle fusion with the Help of BPM. This is an Integrated Tool available in Oracle fusion to design and maintain approval rules as per the business needs.

What is BPM Worklist in Oracle Fusion?

BPM worklist is list of BPM workflows available in Oracle fusion which we can design as per our business Requirement in oracle fusion. We cannot create the new worklist in BPM, We can only use the existing Workflows in the BPM Worklist. In the BPM worklist, We have the approval for Expenses , AP invoice , GL Journals, PO and Requisition AR Invoices and the Cash Advance approvals and many others.

********************************************************

Roles Required for FRS REPORTS?

Financial Application Administrator

General Accounting Manager

Financial Analyst

Application Implementation Manager

********************************************************

ESS Jobs Creation and Manage its definition 

ess job creation process

--> Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications

  --> Manage Job Definitions.

ess jobs lov create

  --> Manage List of value sources

  Create user defined lovs using sql query.

*******************************************************

Roles Required for creating Customer report 

--> BI AUTHOR

--> BI Developer

--> BI Administrator

--> BI Consumer

2 components

1. data model

2. report layout

Components in datamodel

1. data sets

2. triggers

3.bursting

4.lovs

5.parameters

6.flexfields

BI Consumer Role      -->      Runs Business Intelligence reports.

BI Author Role      -->      Creates and edits reports.

BI Administrator Role --> Performs administrative tasks such as creating and editing dashboards and  modifying security permissions for   reports, folders, and so on.

BI Publisher            Data Model Developer Role Creates and edits Oracle Business Intelligence Publisher data models.

******************************************************************

How to get User Run time system information in Oracle Fusion Reports

System Variables                    Variables Description

xdo_user_name                     User ID of the user submitting the report. Like Which fusion User running this report

xdo_user_roles                     Roles assigned to the user submitting the report. Like from Which role this report is running.

xdo_user_report_oracle_lang         Report language from the user's account preferences.

xdo_user_report_locale             Report locale from the user's account preferences.

xdo_user_ui_oracle_lang             User interface language from the user's account preferences.

xdo_user_ui_locale                 User interface locale from the user's account preferences.

Select  :xdo_user_name

              :xdo_user_roles

From Dual

******************************************************

How To Create DFF In Value Set Values Creation Page in Oracle Fusion

Step1:- First We need to go to Setup and Maintenance.

Step2:- In Setup & Maintenance we will go to Manage Descriptive Flexfields.

Step3:- DFF Flex Field code for Value set Value is FND_VS_VALUES_B. Click Edit to configure the DFF.

******************************************************

Default Date in BIP Report Parameter of Oracle BI Cloud

 To default a date in BIP Report parameter you can use sysdate function {$SYSDATE()$} as shown below.

Some times we will also have a requirement to pass date range as 1 week or 1 months. In such cases we can create an expression using plus sign (+) and minus sign (-) to add or subtract days to default the date. For example if we want to default 1 week of date range in from and two date parameters, then we can use of expression.

Date 1: {$SYSDATE()-7$}

Date 2: {$SYSDATE()$}

Similarly, below are the additional functions provided by Oracle BIP.

{$FIRST_DAY_OF_MONTH()$} – first day of the current month

{$LAST_DAY_OF_MONTH()$} – last day of the current month

{$FIRST_DAY_OF_YEAR)$} – first day of the current year

{$LAST_DAY_OF_YEAR)$} – last day of the current year