Friday, 20 September 2019

Function to calculate Item Cost

CREATE OR REPLACE FUNCTION XXTTK_ITEM_COST (P_INV_ITEM_ID IN MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE,
                                           P_INV_ORG_ID    IN MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE )
RETURN NUMBER

AS

L_PERIOD  gmf_period_statuses.PERIOD_CODE%TYPE;
L_COST  NUMBER;


BEGIN

BEGIN

select max(c.period_code)INTO L_PERIOD  FROM cm_cmpt_dtl_vw a,
                                  mtl_system_items b,
                                  gmf_period_statuses c,mtl_lot_numbers mln
                            WHERE 1 = 1          --a.organization_id = :org_id
                           -- AND b.organization_id >= in ( 250,178)
                              AND a.inventory_item_id = b.inventory_item_id
                              AND a.period_id = c.period_id
                              AND a.inventory_item_id = mln.inventory_item_id
                              AND a.organization_id = mln.organization_id
                              AND a.inventory_item_id=P_INV_ITEM_ID;
                             
                              dbms_output.put_line('L_PERIOD '||L_PERIOD);
                             
                              EXCEPTION
                             
                              WHEN OTHERS THEN
                              FND_FILE.PUT_LINE(FND_FILE.LOG,'No Data Found While Capturing the item Cost Period');
                           dbms_output.put_line('No Data Found While Capturing the item Cost Period');
                             
                              end;
                             
                             
                             
                             
                             
                             IF L_PERIOD IS NOT NULL THEN
                           
                             BEGIN
                           
                             SELECT   SUM (cmpnt_cost) INTO L_COST
                             FROM cm_cmpt_dtl_vw a,
                                  mtl_system_items b,
                                  gmf_period_statuses c
                                   --mtl_lot_numbers mln
                            WHERE 1 = 1          --a.organization_id = :org_id
                             AND b.organization_id = P_INV_ORG_ID    --1238
                              AND a.inventory_item_id = b.inventory_item_id
                              AND a.period_id = c.period_id
                             -- AND a.inventory_item_id = mln.inventory_item_id
                              --AND a.organization_id = mln.organization_id
                              AND c.period_code =L_PERIOD
                              AND b.inventory_item_id=P_INV_ITEM_ID
                         GROUP BY a.inventory_item_id,
                                  segment1,
                                  a.period_id,
                                  b.description,
                                  primary_uom_code;
                                 
                                dbms_output.put_line('L_COST '||L_COST);
                             
                              EXCEPTION
                             
                              WHEN OTHERS THEN
                            FND_FILE.PUT_LINE(FND_FILE.LOG,'No Data Found While Capturing the item Cost Period');
                           dbms_output.put_line('No Data Found While Capturing the item Cost Period');
                             
                              end;
                                 
                                  end if;
                                 
                                  Return L_COST;
                                 
                                  EXCEPTION WHEN OTHERS THEN
                                 
                                 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error IN Main XXTTK_ITEM_COST function');
                             
                               dbms_output.put_line('Error IN Main XXTTK_ITEM_COST function');
                                 
                                  Return NULL;
                                 
                                  end;

Thursday, 19 September 2019

Receivables to GL Link in 11i

SELECT je_header_id, line_number, trx_number, myrank, item_number,
       customer_number, customer_name, SOURCE, CATEGORY, batch_name,
       transaction_date, posted_date, period_name, currency_code,
       account_number, description,
       CASE
          WHEN myrank = 1
             THEN debits
          ELSE NULL
       END debits, CASE
          WHEN myrank = 1
             THEN credits
          ELSE NULL
       END credits
  FROM (SELECT   rcta.trx_number, rcta.customer_trx_id, gh.je_header_id,
                 ac.customer_number, ac.customer_name, gh.je_source SOURCE,
                 gh.je_category CATEGORY, gh.NAME batch_name,
                 TO_CHAR (gh.default_effective_date,
                          'DD-MON-YYYY'
                         ) transaction_date,
                 TO_CHAR (gh.posted_date, 'DD-MON-YYYY') posted_date,
                 gh.period_name period_name, gh.currency_code currency_code,
                 gl.je_line_num line_number,
                    gcc.segment1
                 || '.'
                 || gcc.segment2
                 || '.'
                 || gcc.segment3
                 || '.'
                 || gcc.segment4
                 || '.'
                 || gcc.segment5
                 || '.'
                 || gcc.segment6 account_number,
                 gh.description description, gl.accounted_dr debits,
                 gl.accounted_cr credits, rctla.inventory_item_id,
                 (SELECT segment1
                    FROM mtl_system_items_b msib
                   WHERE msib.inventory_item_id =
                                          rctla.inventory_item_id
                     AND msib.organization_id = rctla.warehouse_id)
                                                                  item_number,
                 DENSE_RANK () OVER (PARTITION BY gh.je_header_id, gl.je_line_num ORDER BY rctlg.cust_trx_line_gl_dist_id)
                                                                    AS myrank
            FROM gl_je_headers gh,
                 gl_je_lines gl,
                 gl_code_combinations gcc,
                 fnd_user fu,
                 gl_sets_of_books gsob,
                 gl_import_references gir,
                 ra_customer_trx_all rcta,
                 ra_cust_trx_line_gl_dist_all rctlg,
                 ra_customer_trx_lines_all rctla,
                 ar_customers ac
           WHERE gh.je_header_id = gl.je_header_id
             AND gl.code_combination_id = gcc.code_combination_id
             AND gh.last_updated_by = fu.user_id
             AND gh.period_name = :p_period
             AND gh.status = 'P'
             AND gh.set_of_books_id = gsob.set_of_books_id
             AND gsob.set_of_books_id = 1
             AND gh.je_source = 'Receivables'
             AND gir.je_header_id = gh.je_header_id
             AND gir.je_line_num = gl.je_line_num
             AND gir.je_batch_id = gh.je_batch_id
             AND TO_CHAR (gir.reference_2) = TO_CHAR (rcta.customer_trx_id)
             AND TO_CHAR (gir.reference_3) =
                                      TO_CHAR (rctlg.cust_trx_line_gl_dist_id)
             AND rcta.customer_trx_id = rctlg.customer_trx_id
             AND rctla.customer_trx_line_id = rctlg.customer_trx_line_id
             AND rctlg.customer_trx_id = rctla.customer_trx_id
             AND rctla.customer_trx_id = rcta.customer_trx_id
             AND rcta.bill_to_customer_id = ac.customer_id(+)
        ORDER BY gh.je_header_id, gl.je_line_num)

Script for getting Sales Tax Transactions

SELECT DISTINCT aia.invoice_date transaction_date,
                aia.invoice_num transaction_number, hou.NAME company_name,               
                zl.tax_rate_code tax_code, zl.tax_rate tax_rate,
                (SELECT meaning
                   FROM fnd_lookup_values_vl
                  WHERE lookup_code = UPPER (zl.event_class_code)
                    AND lookup_type = 'ZX_TRL_TAXABLE_TRX_TYPE'
                    AND enabled_flag = 'Y') transaction_class,               
                aia.gl_date accounting_date,
                psv.vendor_name third_party, pss.vendor_site_code site,
                zl.tax_amt tax_amount, zl.trx_currency_code entered_curr,
                zl.taxable_amt_funcl_curr tax_base_amount,
                NVL (zl.tax_currency_code, zl.trx_currency_code) functional_currency,
                tax_amt_funcl_curr tax_amount_func,
                xle.accounting_status, xle.tgl GL_Transfer_Status
           FROM ap_invoices_all aia,
                ap_invoice_lines_all ail,
                poz_suppliers_v psv,
                poz_supplier_sites_v pss,
                zx_lines zl,
                xle_entity_profiles hou,
                (SELECT CASE
                           WHEN xae.gl_transfer_date IS NULL
                              THEN 'Not Transferred'
                           ELSE 'Transferred'
                        END tgl,
                        xte.source_id_int_1,
                        CASE
                           WHEN xae.accounting_date IS NULL
                              THEN 'Draft'
                           ELSE 'Accounted'
                        END accounting_status
                   FROM xla_transaction_entities xte, xla_ae_headers xae
                  WHERE xte.entity_id = xae.entity_id) xle
          WHERE aia.invoice_id = ail.invoice_id
            AND aia.vendor_id = psv.vendor_id
            AND aia.vendor_site_id = pss.vendor_site_id
            AND ail.invoice_id = zl.trx_id(+)
            AND ail.line_number = zl.trx_line_number(+)
            AND hou.legal_entity_id(+) = aia.legal_entity_id           
            AND zl.cancel_flag <> 'Y'
            AND aia.cancelled_date IS NULL
UNION
SELECT DISTINCT rct.trx_date transaction_date,
                rct.trx_number transaction_number, hou.NAME company_name,               
                zl.tax_rate_code tax_code, zl.tax_rate tax_rate,
                (SELECT meaning
                   FROM fnd_lookup_values_vl
                  WHERE lookup_code = zl.event_class_code
                    AND lookup_type = 'ZX_TRL_TAXABLE_TRX_TYPE'
                    AND enabled_flag = 'Y') transaction_class,               
                gl_date accounting_date,
                hca.account_name third_party,
                NVL (hps.party_site_name, hps.party_site_number) site,
                zl.tax_amt tax_amount, zl.trx_currency_code entered_curr,
                zl.taxable_amt_funcl_curr tax_base_amount,
                NVL (zl.tax_currency_code, zl.trx_currency_code ) functional_currency,
                tax_amt_funcl_curr tax_amount_func,
                xle.accounting_status, xle.tgl GL_Transfer_Status
           FROM ra_customer_trx_all rct,
                ra_customer_trx_lines_all rctl,
                zx_lines zl,
                xle_entity_profiles hou,
                hz_cust_site_uses_all hcsua,
                hz_cust_acct_sites_all hcasa,
                hz_cust_accounts hca,
                hz_party_sites hps,
                (SELECT DISTINCT customer_trx_line_id, gl_date,
                                 customer_trx_id
                            FROM ra_cust_trx_line_gl_dist_all) dist_tab,
                (SELECT CASE
                           WHEN xae.gl_transfer_date IS NULL
                              THEN 'Not Transferred'
                           ELSE 'Transferred'
                        END tgl,
                        xte.source_id_int_1,
                        CASE
                           WHEN xae.accounting_date IS NULL
                              THEN 'Draft'
                           ELSE 'Accounted'
                        END accounting_status
                   FROM xla_transaction_entities xte, xla_ae_headers xae
                  WHERE xte.entity_id = xae.entity_id) xle
          WHERE rct.customer_trx_id = rctl.customer_trx_id
            AND rct.customer_trx_id = zl.trx_id(+)
            AND rctl.line_number = zl.trx_line_number(+)
            AND hou.legal_entity_id(+) = rct.legal_entity_id
            AND zl.entity_code = 'TRANSACTIONS'
            AND zl.cancel_flag <> 'Y'
            AND rctl.line_type = 'LINE'
            AND hcsua.site_use_id = rct.bill_to_site_use_id
            AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
            AND hcasa.cust_account_id = hca.cust_account_id
            AND hps.party_site_id = hcasa.party_site_id
            AND dist_tab.customer_trx_id = rct.customer_trx_id
            AND dist_tab.customer_trx_line_id = rctl.customer_trx_line_id
            

Script for Extracting InterCompany batch details

SELECT DISTINCT xep.NAME provider, ftb.status batch_status,
                        ftb.batch_number batch_number,
                        ftb.description batch_description,
                        ftb.batch_date batch_date,
                        ftb.gl_date accounting_date,
                        fttb.trx_type_code transaction_type,
                        (SELECT xep2.NAME
                           FROM xle_entity_profiles xep2
                          WHERE 1 = 1
                            AND xep2.legal_entity_id = fth.to_le_id) receiver,
                        fth.trx_number transaction_number,                       
                        DECODE (party_type_flag,
                                'I', 'Provider',
                                'R', 'Receiver'
                               ) distribution,
                        fdl.dist_number distribution_number,
                        ftb.currency_code currency,
                        NVL (fdl.amount_dr,
                             -1 * fdl.amount_cr) entered_amount,
                        (SELECT gll.currency_code
                           FROM gl_ledgers gll,
                                gl_je_batches gjb,
                                gl_je_headers gjh,
                                gl_je_lines gjl,
                                gl_import_references gir
                          WHERE gll.ledger_id = gjh.ledger_id
                            AND gjb.je_batch_id = gjh.je_batch_id
                            AND gjh.je_header_id = gjl.je_header_id
                            AND gir.reference_2 = ftb.batch_id
                            AND gir.reference_3 = fth.trx_id
                            AND gir.reference_4 = ftl.line_id
                            AND gir.reference_5 = fdl.dist_id
                            AND gir.je_batch_id = gjb.je_batch_id
                            AND gir.je_header_id = gjh.je_header_id
                            AND gir.je_line_num = gjl.je_line_num
                            AND (   gjh.ledger_id = fth.to_ledger_id
                                 OR gjh.ledger_id = ftb.from_ledger_id
                                )
                            AND ROWNUM = 1) primary_currency,
                        (SELECT NVL (gjl.accounted_dr,
                                     -1 * gjl.accounted_cr
                                    )
                           FROM gl_je_batches gjb,
                                gl_je_headers gjh,
                                gl_je_lines gjl,
                                gl_import_references gir
                          WHERE gjb.je_batch_id = gjh.je_batch_id
                            AND gjh.je_header_id = gjl.je_header_id
                            AND gir.reference_2 = ftb.batch_id
                            AND gir.reference_3 = fth.trx_id
                            AND gir.reference_4 = ftl.line_id
                            AND gir.reference_5 = fdl.dist_id
                            AND gir.je_batch_id = gjb.je_batch_id
                            AND gir.je_header_id = gjh.je_header_id
                            AND gir.je_line_num = gjl.je_line_num
                            AND (   gjh.ledger_id = fth.to_ledger_id
                                 OR gjh.ledger_id = ftb.from_ledger_id
                                )
                            AND ROWNUM = 1) primary_amount,
                        fdl.description
                   FROM xle_entity_profiles xep,
                        fun_trx_batches ftb,
                        fun_trx_types_b fttb,
                        fun_trx_headers fth,
                        fun_trx_lines ftl,
                        fun_dist_lines fdl
                  WHERE 1 = 1
                    AND xep.legal_entity_id = ftb.from_le_id
                    AND fttb.trx_type_id = ftb.trx_type_id
                    AND ftb.batch_id = fth.batch_id
                    AND fdl.trx_id = fth.trx_id
                    AND ftl.trx_id = fth.trx_id
                    AND ftl.line_id = fdl.line_id
                    AND ftb.trx_type_code = 'IC_GL'

Script to get Project Listing

SELECT   pt.project_id, ppa.NAME project_name,
         ppa.description project_description, ppa.segment1 project_number,
         pt.task_number,
         ppa.project_status_code project_status,
         (SELECT DISTINCT (SELECT person_number
                             FROM per_people_x ppx
                            WHERE ppx.person_id =
                                                ppn.person_id)
                     FROM pjf_project_parties proj_dir,
                          pjf_proj_role_types_tl prt,
                          per_all_assignments_f paf,
                          per_person_names_f ppn
                    WHERE proj_dir.project_id = ppa.project_id
                      AND proj_dir.project_role_id = prt.project_role_id
                      AND prt.project_role_name = 'Project Manager'
                      AND proj_dir.resource_source_id = paf.person_id
                      AND paf.assignment_status_type = 'ACTIVE'
                      AND paf.person_id = ppn.person_id
                      AND ppn.name_type = 'GLOBAL'
                      AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
                                              AND NVL (paf.effective_end_date,
                                                       SYSDATE + 1
                                                      )
                      AND TRUNC (SYSDATE) BETWEEN proj_dir.start_date_active
                                              AND NVL
                                                    (proj_dir.end_date_active,
                                                     SYSDATE + 1
                                                    )
                      AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date
                                              AND NVL (ppn.effective_end_date,
                                                       SYSDATE + 1
                                                      )
                      AND paf.primary_flag = 'Y'
                      AND ROWNUM < 2) project_manager
    FROM pjf_tasks_v pt ,
         pjf_projects_all_vl ppa
   WHERE 1 = 1 AND ppa.project_id = pt.project_id(+)         

Wednesday, 18 September 2019

Fusion HCM - Payroll Setup Sequence

Below are the Setup Sequence for Fusion Payroll. It differs as per the Client requirement.



1. Manage Payroll Calculation Card
2. Manage Payment Methods
3. Manage Salary Basis
4. Manage Consolidation Groups
5. Manage Payroll Definitions
6. Manage Payroll Balances
7. Manage Object Groups
8. Manage Payroll Event Groups
9. Manage Elements

Fusion HCM - Absence Management Setup Sequence


Below are the Setup Sequence for Fusion Absence Management. It differs as per the Client requirement.


1. Manage Work Shifts
2. Manage Work Workday Patterns
3. Manage Work Schedules
4. Manage Work Schedule Assignment Administration
5. Manage Geography Trees
6. Manage Calendar Events
7. Manage Eligibility Profiles
8. Manage Derived Factors
9. Manage Absence Reasons
10. Manage Absence Certifications
11. Manage Repeating Time Periods
12. Manage Absence Plans
13. Manage AbsenceTypes