Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 29 August 2019

Query to Get Assets Financial Information in Oracle Apps R12

0 comments

SELECT fab.asset_number asset,
       fat.description asset_name,
       fab.asset_type,
       fai.invoice_number,
       fai.po_number,
       (SELECT pla.line_num
        FROM   po_lines_all pla,
               po_distributions_all pda,
               ap_invoice_distributions_all aida
        WHERE  pda.po_line_id = pla.po_line_id
        AND    aida.po_distribution_id = pda.po_distribution_id
        AND    aida.invoice_distribution_id = fai.invoice_distribution_id)
                                                                    po_line_num,
       (SELECT replace(pla.item_description, chr(10) , ' ')
        FROM   po_lines_all pla,
               po_distributions_all pda,
               ap_invoice_distributions_all aida
        WHERE  pda.po_line_id = pla.po_line_id
        AND    aida.po_distribution_id = pda.po_distribution_id
        AND    aida.invoice_distribution_id = fai.invoice_distribution_id)
                                                            po_line_description,
       aps.vendor_name,
       aps.segment1 supplier_number,
       replace(fai.description, chr(10) , ' ')description,
       fai.fixed_assets_cost line_amount,
       fai.invoice_line_number invoice_line,
       fai.ap_distribution_line_number fa_dist_line,
       fai.deleted_flag active,
       fai.payables_batch_name source_batch,
       fai.project_id project_number,
       fai.task_id task_number
FROM   fa_additions_b fab,
       fa_additions_tl fat,
       fa_asset_invoices fai,
       ap_suppliers aps,
       fa_books fb
WHERE  fab.asset_id = fat.asset_id
AND    fai.asset_id = fat.asset_id
AND    aps.vendor_id(+) = fai.po_vendor_id
AND    fb.asset_id = fai.asset_id
AND    fai.date_ineffective IS NULL
AND    fb.date_ineffective IS NULL

Continue reading >>

Query to get Corporate Card Inter-Company Accounting Details in Oracle apps R12

0 comments

SELECT DISTINCT aeh.invoice_num exp_rept_num
              , aeh.report_header_id
              , ael.report_line_id
              , ael.merchant_name
              , ( SELECT segment1
                 FROM   po_vendors pv
                      , ap_invoices_all aia
                 WHERE  pv.vendor_id = aia.vendor_id
                 AND    aia.invoice_id = aila.invoice_id ) vendor_num
              ,
                glcc.segment1 company_code
              , glcc.segment2 dept_code
              , paf.full_name employee_name
              , xah.accounting_date
              , ael.creation_date expense_date
              , CASE
                   WHEN NVL( UPPER( :p_expense_type ), 'A' ) IN
                                                             ( 'Y', 'N', 'A' )
                      THEN CASE
                             WHEN ael.credit_card_trx_id IS NOT NULL
                                THEN 'Credit Card'
                             WHEN ael.credit_card_trx_id IS NULL
                                THEN 'Out Of Pocket and Per-diem'
                          END
                END expense_type
              , aerp.prompt expense_item
              , xdl.unrounded_entered_dr debit
              , xdl.unrounded_entered_cr credit
              , (   NVL( xdl.unrounded_entered_dr, 0 )
                  - NVL( xdl.unrounded_entered_cr, 0 )
                ) dr_cr
              , glcc1.segment6 accounting_company_code
              , xal.code_combination_id
              , xal.ae_line_num
              ,    glcc1.segment1
                || '.'
                || glcc1.segment2
                || '.'
                || glcc1.segment3
                || '.'
                || glcc1.segment4
                || '.'
                || glcc1.segment5
                || '.'
                || glcc1.segment6
                || '.'
                || glcc1.segment7 charge_account
              -- Added by GSINGH FOR Version 1.5
,               aeh.week_end_date
              , ap_web_policy_utils.get_lookup_meaning
                                        ( 'EXPENSE REPORT STATUS'
                                        , DECODE
                                               ( ( SELECT ai.cancelled_date
                                                  FROM   ap_invoices_all ai
                                                  WHERE  ai.invoice_id =
                                                                   aeh.vouchno )
                                               , NULL, aeh.expense_status_code
                                               , 'CANCELLED'
                                               )
                                        ) report_status
              , ap_web_policy_utils.get_lookup_meaning( 'OIE_AUDIT_TYPES'
                                                      , NVL( aeh.audit_code
                                                           , 'AUDIT'
                                                           )
                                                      ) audit_type
              , ap_web_policy_utils.get_lookup_meaning( 'RECEIPT_STATUS'
                                                      , aeh.receipts_status
                                                      )
                                                       receipt_package_status
              , ( SELECT DISTINCT LISTAGG(ppa.segment1,'~')  WITHIN GROUP (ORDER BY  ppa.segment1) a
                 FROM   pa_projects_all ppa
                      , ap_exp_report_dists_all aerd
                 WHERE  ppa.project_id = aerd.project_id
                 AND    aeh.report_header_id = aerd.report_header_id
                 AND    aerd.report_line_id = ael.report_line_id )
                                                               project_number
        ,(SELECT DISTINCT LISTAGG(pt.task_number,'~')  WITHIN GROUP (ORDER BY  task_number) a FROM  pa_tasks pt
                      , ap_exp_report_dists_all aerd
                 WHERE  pt.task_id = aerd.task_id
                   AND    aerd.report_header_id = aerd.report_header_id
                 AND    aerd.report_line_id =ael.report_line_id ) task_number  
              , ( SELECT LISTAGG(ho.name,'~')  WITHIN GROUP (ORDER BY  ho.name) a
                 FROM   hr_all_organization_units ho
                      , ap_exp_report_dists_all aerd
                 WHERE  ho.organization_id = aerd.expenditure_organization_id
                 AND    aeh.report_header_id = aerd.report_header_id
                 AND    aerd.report_line_id = ael.report_line_id ) exp_org
              , ael.justification
-- END
FROM            ap_expense_report_headers_all aeh
              , ap_expense_report_lines_all ael
              , ap_expense_report_params_all aerp
              , ap_invoice_distributions_all aid
              , xla_ae_headers xah
              , xla_ae_lines xal
              , xla_distribution_links xdl
              , ap_invoice_lines_all aila
              , per_all_people_f paf
              , per_all_assignments_f pasf
              , gl_code_combinations glcc
              , gl_code_combinations glcc1
WHERE           aeh.report_header_id = ael.report_header_id
AND             aeh.vouchno != 0
AND             EXISTS(
                   SELECT 'X'
                   FROM   ap_expense_report_lines_all
                   WHERE  report_line_id = ael.report_line_id
                   AND    UPPER( :p_expense_type ) = 'Y'
                   AND    credit_card_trx_id IS NOT NULL )
AND             xal.code_combination_id = glcc1.code_combination_id
AND             aeh.employee_id = paf.person_id
AND             TRUNC( SYSDATE ) BETWEEN TRUNC( paf.effective_start_date )
                                     AND TRUNC( paf.effective_end_date )
AND             paf.person_id = pasf.person_id
AND             TRUNC( ael.creation_date )
                   BETWEEN TRUNC( pasf.effective_start_date )
                       AND TRUNC( pasf.effective_end_date )
AND             pasf.default_code_comb_id = glcc.code_combination_id
AND             ael.web_parameter_id = aerp.parameter_id(+)
AND             aeh.vouchno = aid.invoice_id
AND             aid.line_type_lookup_code = 'ITEM'
AND             aid.accounting_event_id = xah.event_id
AND             aid.invoice_distribution_id = xdl.source_distribution_id_num_1
AND             xah.ae_header_id = xal.ae_header_id
AND             xal.ae_line_num = xdl.ae_line_num
AND             xal.ae_header_id = xdl.ae_header_id
AND             xah.ae_header_id = xdl.ae_header_id
AND             xah.event_id = xdl.event_id
AND             xdl.rounding_class_code = 'ITEM EXPENSE'
AND             aeh.vouchno = aila.invoice_id
AND             ael.report_line_id = aila.reference_key2
AND             aila.line_type_lookup_code = 'ITEM'
AND             aila.line_number = aid.invoice_line_number                                    

Continue reading >>

Query to Get Employee Corporate Card Census Details in Oracle Apps R12

0 comments

SELECT   papf.employee_number
        ,papf.full_name
        ,haou.NAME department
        ,hrl.location_code LOCATION
        ,acpa.card_program_name card_program
        ,icc.masked_cc_number credit_card_num
        ,papf1.full_name supervisor
        ,past.user_status assignment_status
        ,TO_CHAR (icc.expirydate, 'DD-MON-YYYY') expiry_date
        ,TO_CHAR (aca.inactive_date, 'DD-MON-YYYY') inactive_on
        ,DECODE (icc.expired_flag, 'Y', 'Expired', 'N', 'Unexpired')
                                                                  expiry_status
        ,aca.limit_override_amount max_amt_per_period
        ,aca.trx_limit_override_amount max_amt_per_trans
FROM     ap_cards_all aca
        ,ap_card_programs_all acpa
        ,iby_creditcard icc
        ,per_all_people_f papf
        ,per_all_assignments_f paaf
        ,per_assignment_status_types past
        ,hr_all_organization_units haou
        ,hr_locations hrl
        ,per_all_people_f papf1
WHERE    1 = 1
AND      aca.card_program_id = acpa.card_program_id
AND      aca.card_reference_id = icc.instrid
AND      aca.employee_id = papf.person_id
AND      papf.person_id = paaf.person_id
AND      paaf.assignment_status_type_id = past.assignment_status_type_id
AND      paaf.organization_id = haou.organization_id
AND      paaf.location_id = hrl.location_id
AND      paaf.supervisor_id = papf1.person_id
AND      SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND      SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND      SYSDATE BETWEEN papf1.effective_start_date AND papf1.effective_end_date
--AND      hrl.location_id = NVL (:P_EMP_LOC, hrl.location_id)
--AND      haou.organization_id = NVL (:P_EMP_DEPT, haou.organization_id)
---AND      aca.creation_date BETWEEN NVL (TO_DATE (SUBSTR ----(:P_FROM_DATE, 1, 10)
                                                ,'RRRR-MM-DD')
                                       , aca.creation_date - 1)
                             ---  AND NVL (TO_DATE (SUBSTR (:P_TO_DATE, 1, 10)
                                                ,'RRRR-MM-DD')
                                        + 86399 / 86400
                                       , aca.creation_date + 1)
---AND      :P_EXCLUDE_EXP_CARD = 'Y'
AND      NVL (aca.inactive_date, SYSDATE + 1) > SYSDATE


Continue reading >>

Query to get AP Distributions by Account in Oracle Apps R12

0 comments
SELECT DISTINCT

         APS.VENDOR_NAME,
         APS.SEGMENT1 VENDOR_NUMBER,
         API.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,
         API.INVOICE_NUM,
         POH.SEGMENT1 PO_NUMBER,
         API.INVOICE_DATE,
         API.GL_DATE,        
         API.INVOICE_AMOUNT,
         API.AMOUNT_PAID,
         API.PAYMENT_METHOD_CODE,
         API.REMIT_TO_SUPPLIER_NAME,
         APSS.ADDRESS_LINE1 ||', ' || APSS.ADDRESS_LINE2 ||', ' || APSS.CITY ||', ' || APSS.STATE ||', ' || APSS.ZIP ||', ' ||APSS.COUNTRY REMIT_TO_ADDR,
         API.CANCELLED_DATE,
         APL.LINE_NUMBER,
         APL.DESCRIPTION,
         APL.QUANTITY_INVOICED,
         APD.AMOUNT DIST_AMOUNT,
          APD.DIST_CODE_COMBINATION_ID AP_CCID,
         APD.PERIOD_NAME,        
         POD.CODE_COMBINATION_ID PO_CCID ,
           CC.SEGMENT1 || '-' ||
           CC.SEGMENT2 || '-' ||
           CC.SEGMENT3 ||'-' ||
           CC.SEGMENT4 ||'-' ||
           CC.SEGMENT5 ||'-' ||
           CC.SEGMENT6 PO_DIST_ACCT,
           CC.SEGMENT3 PO_ACCOUNT ,
           FVL.DESCRIPTION PO_ACC_DESC,
           CC1.SEGMENT1 ||'-' ||
           CC1.SEGMENT2 ||'-' ||
           CC1.SEGMENT3 ||'-' ||
           CC1.SEGMENT4 ||'-' ||
           CC1.SEGMENT5 ||'-' ||
           CC1.SEGMENT6 AP_DETAIL_DIST_ACCT,
           CC1.SEGMENT3 AP_ACCOUNT ,
           FVL1.DESCRIPTION AP_ACC_DESC,
                  API.PAYMENT_STATUS_FLAG,
           DECODE(API.PAYMENT_STATUS_FLAG,'Y' ,'FULLY PAID', 'N', 'NOT PAID','P','PARTIALLY PAID') PAYMENT_STATUS
           , AP_INVOICES_PKG.GET_APPROVAL_STATUS (API.INVOICE_ID,
                                                 API.INVOICE_AMOUNT,
                                                 API.PAYMENT_STATUS_FLAG,
                                                 API.INVOICE_TYPE_LOOKUP_CODE) INVOICE_STATUS
           , (SELECT COUNT(PK1_VALUE) FROM FND_ATTACHED_DOCUMENTS FAD WHERE PK1_VALUE = API.INVOICE_ID
           AND ENTITY_NAME = 'AP_INVOICES') ATTACHMENTS
          ,APAY.ACCOUNTING_DATE PAYMENT_DATE
          ,APAY.VOID
          ,API.CREATION_DATE
          ,FNDU.USER_NAME
          ,APD.DESCRIPTION DIST_DESC
                     , (select CONCATENATED_SEGMENTS from gl_code_combinations_kfv where  code_combination_id   =   pod.DEST_CHARGE_ACCOUNT_ID ) DES_CHR_ACNT
  FROM   AP_INVOICES_ALL API,
         AP_INVOICE_LINES_ALL APL,
         AP_INVOICE_DISTRIBUTIONS_ALL APD,
         PO_HEADERS_ALL POH,
         PO_LINES_ALL POL,
         PO_DISTRIBUTIONS_ALL POD,
         AP_SUPPLIERS APS,
         AP_SUPPLIER_SITES_ALL APSS,
         GL_CODE_COMBINATIONS CC,
         GL_CODE_COMBINATIONS CC1,
         FND_FLEX_VALUES_VL FVL  ,
         FND_FLEX_VALUES_VL FVL1 ,
         FND_USER FNDU,
         AP_INVOICE_PAYMENT_HISTORY_V APAY
 WHERE      1=1
         AND API.INVOICE_ID = APL.INVOICE_ID
         AND API.INVOICE_ID = APD.INVOICE_ID
         AND APL.INVOICE_ID = APD.INVOICE_ID
         AND APL.MATCH_TYPE IN ('ITEM_TO_RECEIPT','ITEM_TO_PO')
         AND APD.INVOICE_LINE_NUMBER = APL.LINE_NUMBER
         AND APL.PO_HEADER_ID = POH.PO_HEADER_ID
         AND APL.PO_LINE_ID = POL.PO_LINE_ID
         AND POD.PO_LINE_ID = POL.PO_LINE_ID
         AND CC.CODE_COMBINATION_ID = POD.CODE_COMBINATION_ID
         AND CC1.CODE_COMBINATION_ID = APD.DIST_CODE_COMBINATION_ID
         AND POH.VENDOR_ID = APS.VENDOR_ID
        -- AND CC1.SEGMENT1 BETWEEN NVL(:AP_COMPANY_FROM,CC1.SEGMENT1) AND NVL(:AP_COMPANY_TO,CC1.SEGMENT1)
        -- AND CC.SEGMENT1 BETWEEN NVL(:PO_COMPANY_FROM,CC.SEGMENT1) AND NVL(:PO_COMPANY_TO,CC.SEGMENT1)
        -- AND CC.SEGMENT3 BETWEEN NVL(:PO_ACCOUNT_FROM,CC.SEGMENT3) AND NVL(:PO_ACCOUNT_TO,CC.SEGMENT3)
        -- AND CC1.SEGMENT3 BETWEEN NVL(:AP_ACCOUNT_FROM,CC1.SEGMENT3) AND NVL(:AP_ACCOUNT_TO,CC1.SEGMENT3)
                   -- Added by GSINGH for Request ID 72815
                   -- AND NVL(POL.ITEM_ID,'9999') = DECODE(:P_INV_ITEMS, 'INV', NVL(POL.ITEM_ID,'00'),'BOTH',NVL(POL.ITEM_ID,'9999'),'EXP','9999')
                   -- AND (API.ORG_ID = :P_ORG_ID OR :P_ORG_ID IS NULL)
                   -- AND (API.INVOICE_TYPE_LOOKUP_CODE  = :P_INV_TYPE OR :P_INV_TYPE IS NULL)
        --                 
         --AND API.CREATION_DATE BETWEEN NVL(:P_CR_DATE_FRM,SYSDATE - 9999) AND NVL(:P_CR_DATE_TO ,SYSDATE + 999)
        -- AND API.GL_DATE BETWEEN :P_GL_DATE_F AND :P_GL_DATE_T
        -- AND APS.VENDOR_NAME BETWEEN NVL(:P_VENDOR_NAME_FROM,APS.VENDOR_NAME) AND NVL(:P_VENDOR_NAME_TO,APS.VENDOR_NAME)
         AND FVL.FLEX_VALUE = CC.SEGMENT3
         --AND FVL.FLEX_VALUE_SET_ID =   1013629
         AND FVL1.FLEX_VALUE = CC1.SEGMENT3
         --AND FVL1.FLEX_VALUE_SET_ID =   1013629
         AND FNDU.USER_ID = API.CREATED_BY    
         AND APAY.INVOICE_ID(+) = API.INVOICE_ID
         ---AND FNDU.USER_NAME = NVL(:P_USER,FNDU.USER_NAME) 
         AND APSS.VENDOR_ID = API.VENDOR_ID
         AND APSS.VENDOR_SITE_ID = API.VENDOR_SITE_ID

Continue reading >>