Sales GP & Commission

Commission Supporting data:-

SELECT 

        DISTINCT 

LTRIM(dha.order_number)                      ORDER_NUMBER, 

        NULL                                               PROJECT_NAME,

        dla.line_number                                   LINE_NUMBER,

        TO_CHAR(dla.CREATION_DATE, 'mm/dd/yyyy')           LINE_CREATION_DATE,

        dheb.attribute_char1                   Selling_Location,

        NULL                                               Sub_vertical,

        (SELECT LISTAGG(DISTINCT dfl.attribute_char4, ',')

            FROM 

                doo_lines_all dla

                ,doo_fulfill_lines_eff_b dfl

                ,doo_fulfill_lines_all dfla

                ,ra_customer_trx_lines_all rctl

            WHERE

                dfla.fulfill_line_id = dfl.fulfill_line_id

                AND dfla.line_id = dla.line_id

                AND rctl.interface_line_attribute5 = dfla.fulfill_line_id

                AND rctl.customer_trx_id = trx.customer_trx_id

                AND dfl.context_code = 'Standard Line'

            GROUP BY 

                trx.interface_header_attribute1)           OSR,

        NULL                                               OSR_USER_ID,

        (SELECT 

                    LISTAGG(DISTINCT dfl.attribute_char5, ',')

            FROM 

                doo_lines_all dla

                ,doo_fulfill_lines_eff_b dfl

                ,doo_fulfill_lines_all dfla

                    ,ra_customer_trx_lines_all rctl

            WHERE

                dfla.fulfill_line_id = dfl.fulfill_line_id

                AND dfla.line_id = dla.line_id

                AND rctl.interface_line_attribute5 = dfla.fulfill_line_id

                AND rctl.customer_trx_id = trx.customer_trx_id

                AND dfl.context_code = 'Standard Line'

            GROUP BY 

                trx.interface_header_attribute1)                     ISR,

        NULL                                                         ISR_USER_ID,

        trx.trx_number                             INVOICE_NUMBER,

        trxl.line_number                                 INVOICE_LINE_NUMBER,

        TO_CHAR(trx.trx_date, 'mm/dd/yyyy')                   INVOICE_DATE,

        xah.period_name                                                 period_name,

        hp_bill.party_name     BILL_TO_CUSTOMER,

        bill_cust.account_number                                        BILL_TO_CUSTOMER_NUMBER,

        (SELECT  hp.party_name 

                                FROM    doo_order_addresses             doa

                                    ,   hz_parties                      hp

                                    ,   hz_cust_acct_sites_all          hcasa

                                    ,   hz_cust_accounts                hca

                                WHERE   doa.header_id                   = dha.header_id

                                    AND doa.address_use_type            = 'SHIP_TO'

                                    AND hcasa.party_site_id             = doa.party_site_id

                                    AND hca.cust_account_id             = hcasa.cust_account_id

                                    AND hp.party_id                     = doa.party_id

                                    AND rownum                          = 1) SHIP_TO_CUSTOMER,

        ( SELECT  hca.account_number

                                FROM    doo_order_addresses             doa

                                    ,   hz_parties                      hp

                                    ,   hz_cust_acct_sites_all          hcasa

                                    ,   hz_cust_accounts                hca

                                WHERE   doa.header_id                   = dha.header_id

                                    AND doa.address_use_type            = 'SHIP_TO'

                                    AND hcasa.party_site_id             = doa.party_site_id

                                    AND hca.cust_account_id             = hcasa.cust_account_id

                                    AND hp.party_id                     = doa.party_id

                                    AND rownum                          = 1)          SHIP_TO_CUSTOMER_NUMBER,

    dla.line_type_code                             LINE_TYPE,

        esib.ITEM_TYPE                                         ITEM_TYPE,

        esib.ITEM_NUMBER                         ITEM,

        esib.description                         DESCRIPTION,

        NULL                                                         SOURCE_TYPE,

        (SELECT   pssam.vendor_site_code

                FROM        po_headers_all              poh 

,   po_lines_all pol

                        ,   poz_suppliers               ps

                        ,   poz_supplier_sites_all_m    pssam

                WHERE    rownum = 1

                        AND pol.attribute2          = dha.order_number||'-'||dla.line_number

                        AND ps.vendor_id            = poh.vendor_id

AND pol.po_header_id        = poh.po_header_id                       

                        AND pssam.vendor_id         = ps.vendor_id

                        AND pssam.vendor_site_id    = poh.vendor_site_id

        )

                                                                        VENDOR_SITE_CODE,

        NULL                                                         PRODUCT_CODE,                           

        dfla.unit_selling_price                     Sale_Price,     

    NVL(dfla.fulfilled_qty * dfla.UNIT_SELLING_PRICE ,0)         SALE_AMOUNT,

        dfl.attribute_char1                           Standard_Inv_COST_AMOUNT,

        NULL                                                            standard_freight_in_cost_amount,

        NULL                                                         standard_spa_cost_amount,

    (   SELECT  round(cpc.unit_cost_average,2)

            FROM    cst_transactions                ct

                ,   cst_layer_costs                 clc

                ,   cst_perpavg_cost                cpc

            WHERE   ct.doo_fullfill_line_id         = dfla.fulfill_line_id

                AND clc.transaction_id              = ct.transaction_id

                AND cpc.perpavg_cost_id             = clc.perpavg_cost_id)

                                                                        average_cost ,

        NULL                                            Sale_VS_Standard_GP,

        NULL                                ADJUSTMENT_TYPE ,

        NULL                                global_account_price_code,

        dfla.fulfilled_qty                            SHIPPED_QUANTITY,  

        NULL                                OTHER_SALES,

gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5||'-'||gcc.segment6||'-'||gcc.segment7||'-'||gcc.segment8||'-'||gcc.segment9 

                                                                        ORACLE_COA,

        flv.meaning                                                     accounting_class

FROM ra_customer_trx_all               trx

     ,doo_lines_all                    dla

,doo_fulfill_lines_eff_b          dfl

     ,doo_fulfill_lines_all            dfla

,doo_headers_eff_b   dheb

     ,ra_customer_trx_lines_all        trxl

     ,hz_cust_accounts                 bill_cust

     ,hz_cust_accounts                 ship_cust

     ,egp_system_items           esib

     ,doo_headers_all                  dha

     ,ra_cust_trx_line_gl_dist_all   rctd

,xla_distribution_links   xdl

,xla_ae_lines                     xal

,xla_ae_headers   xah

,gl_code_combinations   gcc

     ,fnd_lookup_values                flv

     ,xla_transaction_entities         xte

,hz_parties   hp_bill

WHERE   1 = 1 

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

            AND flv.lookup_code = xal.accounting_class_code

            AND flv.lookup_type = 'XLA_ACCOUNTING_CLASS'

            AND flv.language    = 'US'

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

            AND trxl.customer_trx_id = trx.CUSTOMER_TRX_ID

            AND dfla.line_id = dla.line_id

            AND trxl.interface_line_attribute5 = dfla.fulfill_line_id

            AND bill_cust.cust_account_id = trx.bill_to_customer_id

            AND hp_bill.party_id = bill_cust.party_id

            and ship_cust.cust_account_id(+) = trx.ship_to_customer_id

            and esib.inventory_item_id = dla.inventory_item_id

            and dha.header_id = dla.header_id

AND dfla.fulfill_line_id = dfl.fulfill_line_id --

    AND dfl.context_code = 'Pricing Attributes'

AND dha.header_id = dheb.header_id

AND dheb.context_code = 'Standard Order' 

AND dha.object_version_number = (

SELECT 

  MAX(object_version_number) 

FROM 

  doo_headers_all dha_latest 

WHERE 

  dha_latest.order_number = dha.order_number 

)                                           

AND rctd.customer_trx_id  = trxl.customer_trx_id    

AND rctd.customer_trx_line_id  =    trxl.customer_trx_line_id 

AND rctd.cust_trx_line_gl_dist_id = xdl.source_distribution_id_num_1 

AND xal.ae_header_id = xdl.ae_header_id

AND xah.ae_header_id = xdl.ae_header_id

AND xah.application_id = xal.application_id

AND xdl.application_id = xal.application_id

AND xdl.ae_line_num = xal.ae_line_num

--AND xdl.application_id = 222

AND gcc.code_combination_id = xal.code_combination_id

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

AND trx.customer_trx_id        = xte.source_id_int_1

AND xte.entity_id              = xah.entity_id

AND xte.application_id         = xah.application_id

ORDER BY 

        LTRIM(dha.order_number)

    ,   dla.line_number

,   trx.trx_number

,   trxl.line_number

===============================================================================================================

Sales GP

SELECT DISTINCT

gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5||'-'||gcc.segment6||'-'||gcc.segment7||'-'||gcc.segment8||'-'||gcc.segment9 

                                                                ORACLE_COA,

            flv.meaning                                         accounting_class,

            LTRIM(dha.order_number)                   ORDER_NUMBER, 

            TO_CHAR(dla.CREATION_DATE, 'mm/dd/yyyy')         LINE_CREATION_DATE,

            dheb.attribute_char1             Selling_Location,

            NULL                         Sub_vertical,

            trx.trx_number                     INVOICE_NUMBER,

            trxl.line_number                 INVOICE_LINE_NUMBER,

            TO_CHAR(trx.trx_date, 'mm/dd/yyyy')                 INVOICE_DATE,

            xah.period_name                                     period_name,

            hp_bill.party_name                                 BILL_TO_CUSTOMER,

            esib.ITEM_NUMBER                 ITEM,

            esib.description                 DESCRIPTION,

            dfla.unit_selling_price                 Sale_Price,     

            NVL(dfla.fulfilled_qty * dfla.UNIT_SELLING_PRICE ,0) SALE_AMOUNT,

            dfl.attribute_char1                   Standard_Inv_COST_AMOUNT,

            NULL                                                standard_freight_in_cost_amount,

            NULL                                             standard_spa_cost_amount,

            NULL                        SPA_Accrual,

            (SELECT  round(cpc.unit_cost_average,2)

            FROM    cst_transactions                ct

                ,   cst_layer_costs                 clc

                ,   cst_perpavg_cost                cpc

            WHERE   ct.doo_fullfill_line_id         = dfla.fulfill_line_id

                AND clc.transaction_id              = ct.transaction_id

                AND cpc.perpavg_cost_id             = clc.perpavg_cost_id)      AVERAGE_COST,

            NULL                        Sale_VS_Standard_GP,

            NULL                                                Standard_Inv_vs_Avg_Cost,

            NULL                                                Standard_SPA_vs_SPA_Accrual,

            NULL                                                Sale_VS_AVG_GP,

            NULL                                                ADJUSTMENT_TYPE,

            dfla.fulfilled_qty                            SHIPPED_QUANTITY,

            NULL                        OTHER_SALES

FROM ra_customer_trx_all               trx

     ,doo_lines_all                    dla

,doo_fulfill_lines_eff_b          dfl

     ,doo_fulfill_lines_all            dfla

,doo_headers_eff_b   dheb

     ,ra_customer_trx_lines_all        trxl

     ,hz_cust_accounts                 bill_cust

     ,egp_system_items           esib

     ,doo_headers_all                  dha

     ,ra_cust_trx_line_gl_dist_all   rctd

,xla_distribution_links   xdl

     ,xla_ae_lines                     xal

     ,xla_ae_headers   xah

     ,gl_code_combinations   gcc

     ,fnd_lookup_values                flv

     ,xla_transaction_entities         xte

,hz_parties                       hp_bill

WHERE   1 = 1 

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

            AND flv.lookup_code = xal.accounting_class_code

            AND flv.lookup_type = 'XLA_ACCOUNTING_CLASS'

            AND flv.language    = 'US'

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

            AND trxl.customer_trx_id = trx.CUSTOMER_TRX_ID

            AND dfla.line_id = dla.line_id

            AND trxl.interface_line_attribute5 = dfla.fulfill_line_id

            AND bill_cust.cust_account_id = trx.bill_to_customer_id

AND hp_bill.party_id       = bill_cust.party_id

            and esib.inventory_item_id = dla.inventory_item_id

            and dha.header_id = dla.header_id

AND dfla.fulfill_line_id = dfl.fulfill_line_id --

    AND dfl.context_code = 'Pricing Attributes'

AND dha.header_id = dheb.header_id

AND dheb.context_code = 'Standard Order' 

AND dha.object_version_number = (

SELECT 

  MAX(object_version_number) 

FROM 

  doo_headers_all dha_latest 

WHERE 

  dha_latest.order_number = dha.order_number 

)                                           

        AND rctd.customer_trx_id  = trxl.customer_trx_id    

AND rctd.customer_trx_line_id  =    trxl.customer_trx_line_id 

AND rctd.cust_trx_line_gl_dist_id = xdl.source_distribution_id_num_1 

AND xal.ae_header_id = xdl.ae_header_id

AND xah.ae_header_id = xdl.ae_header_id

            AND xah.application_id = xal.application_id

            AND xdl.application_id = xal.application_id

AND xdl.ae_line_num = xal.ae_line_num

--AND xdl.application_id = 222

AND gcc.code_combination_id = xal.code_combination_id

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

      AND trx.customer_trx_id        = xte.source_id_int_1

      AND xte.entity_id              = xah.entity_id

      AND xte.application_id         = xah.application_id

ORDER BY 

        LTRIM(dha.order_number)

,   trx.trx_number

    ,   trxl.line_number