Reopen Closed Period

/*  opm_undo_create_accounting_by_date_range_and_LE_R12.sql
+=======================================================================+
 *                     Undo/Reset OPM Costed Flags
 * +=======================================================================+
 *
 * IMPORTANT NOTE:
 * ===============
 *
 * 1. This script should be used by authorised Oracle personnel only.
 *    Use of this script must be approved by:
 *        OPM Financial Manager
 *        OPM Level 2 Support Manager
 *        Customer Management
 *
 * 2. Running this script without control/due care may severly damage the
 *    customer's sub-ledger.
 *  a.  Advice the customer to perform a complete backup and
 *        create a test environement.
 *    b.  Make sure that the customer understands that this script will
 *        only resets costed flags for transactions in process enabled
 *        organizations, so that these transactions can be considered
 *        for processing by ACP/Preprocessor during next runs.
 *    c.  Perform reversing/deleting of GL postings based on GL batches posted.
 *    d.  Run the script in the test environment first.
 *    e.  Ask customer to run the process (ACP/Pre-processor) again.
 *        Verify the results.
 *    f.  IF THE RESULTS ARE ACCEPTABLE RUN THE SCRIPT IN PRODUCTION
 *        ENVIRONMENT.
 *
 * 3. This script must be immediately removed from the customer's machine
 *    as soon as possible.  This is to prevent any accidental execution
 *    by the customer's personnel.
 * 4. Please modify start date ,end date and legal entity id
 *
 *
 * How to Use:
 * ===========
 *   - Make sure you backup all the related tables
 *     mtl_material_transactions
 *     gmf_rcv_accounting_txns
 *     gmf_invoice_distributions
 *     gmf_incoming_material_layers
 *     gme_resource_txns
 *     gme_batch_header
 *     gmf_lot_cost_adjustments
 *     gmf_period_balances
 *     cm_adjs_dtl
 *     gl_aloc_dtl
 *     gmf_lc_actual_cost_adjs  -- Applicable for OPM-LCM integration for R12.1 and later
 *     gmf_lc_lot_cost_adjs      -- Applicable for OPM-LCM integration for R12.1 and later
 *     gmf_lc_adj_transactions  -- Applicable for OPM-LCM integration for R12.1 and later
 *     gmf_transaction_valuation
 *     xla_distribution_links
 *     xla_ae_lines
 *     xla_ae_segment_values
 *     xla_ae_headers
 *     xla_events
 *     xla_transaction_entities_upg
 *     gmf_xla_extract_lines
 *     gmf_xla_extract_headers
 *
 *
 * PRE-REQUISITE:
 *
 *     - Perform reversing/deleting of GL postings based on GL batches posted.
 *         After performing the reversals of batches make sure that the reversal/deleting
 *         of GL postings has happened correctly from GL side. Once that is made sure, only then proceed 
 *         Please log a Service Request with Oracle General Ledger Support team For assistance with reversing/deleting GL Batches
 *
 *       Use THIS script, for next step to reset OPM costed flags.
 *
 *   - Script does not commit. Commit or rollback needs to be explicitly issued
 *         based on the results from the above query.
 *  
 *  CHANGE HISTORY:
 *   Version    Date        Name        Comments   
 *   2.00      01-JUL-2015     KGENIKAL     Modified to add additional scripts needed
 *                        to support LCM-OPM integration tables ( Reference bugs 21054001, 20503780)
 *   
 * +=======================================================================+  */


    --
    -- Update mtl_material_transactions table
    --

        UPDATE mtl_material_transactions t
        SET   opm_costed_flag  = 'D'              
        WHERE transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND   transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND   EXISTS
    (
        SELECT 1
        FROM   mtl_parameters p, hr_organization_information hoi
        WHERE  p.process_enabled_flag      = 'Y'
        AND    hoi.org_information2        = '&LE_ID'
                AND    hoi.org_information_context = 'Accounting Information'
                AND    p.organization_id           = hoi.organization_id
        AND    p.organization_id           = t.organization_id
    );
   
        --
        -- Update gmf_rcv_accounting_txns table
        --      

        UPDATE gmf_rcv_accounting_txns grat
        SET    accounted_flag  = 'D'
        WHERE  transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND    transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND    EXISTS
    (
        SELECT 1
        FROM   mtl_parameters p, hr_organization_information hoi
        WHERE  p.process_enabled_flag      = 'Y'
        AND    hoi.org_information2        = '&LE_ID'
                AND    hoi.org_information_context = 'Accounting Information'
                AND    p.organization_id           = hoi.organization_id
        AND    p.organization_id           = grat.organization_id
    );
       
        --
        -- Update gmf_invoice_distributions table
        -- 

        UPDATE gmf_invoice_distributions
        SET  Accounted_flag = 'D', final_posting_date   = NULL              
        WHERE accounted_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND   accounted_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND   legal_entity_id = '&LE_ID';
               
    --
    --    Update transaction valuation table to set shipment_costed flag
    --    back to NULL for event clases FOB_RCPT_SENDER_RCPT and FOB_SHIP_RECIPIENT_SHIP
    --        
                      
    UPDATE mtl_material_transactions t
    SET    shipment_costed       = NULL               
    WHERE  transaction_source_type_id IN (7, 8, 13)
    AND    transaction_action_id      IN (12, 21)
    AND    transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND    transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND    EXISTS
    (
        SELECT 1
        FROM   mtl_parameters p, hr_organization_information hoi
        WHERE  p.process_enabled_flag      = 'Y'
        AND    hoi.org_information2        = '&LE_ID'
                AND    hoi.org_information_context = 'Accounting Information'
                AND    p.organization_id           = hoi.organization_id
        AND    p.organization_id           = t.organization_id
    );
           

    --
        -- Update incoming layers table (BatchesXperiods Enh.)
        --

    UPDATE gmf_incoming_material_layers giml
    SET    accounted_flag        = 'D',
           actual_posting_date   =  NULL
    WHERE  layer_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND    layer_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND    EXISTS
    (
        SELECT 1
        FROM   mtl_parameters p, hr_organization_information hoi
        WHERE  p.process_enabled_flag      = 'Y'
        AND    hoi.org_information2        = '&LE_ID'
                AND    hoi.org_information_context = 'Accounting Information'
                AND    p.organization_id           = hoi.organization_id
        AND    p.organization_id           = giml.mmt_organization_id
    );
       
        --
        -- Update resource transaction table
        -- Decide what Flag to use when run in Draft Mode.
        --       

    UPDATE gme_resource_txns
    SET   posted_ind  = 0               
    WHERE trans_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND   trans_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND   organization_id in (
        select hoi.organization_id
                from   hr_organization_information hoi
                where  hoi.org_information2 = '&LE_ID'
                and    hoi.org_information_context = 'Accounting Information'
    );
       
    --
    -- Update Batch Header table
    --
                  
    UPDATE gme_batch_header
    SET   gl_posted_ind     = 0                   
    WHERE batch_close_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND   batch_close_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND  organization_id in (
        select hoi.organization_id
                from   hr_organization_information hoi
                where  hoi.org_information2 = '&LE_ID'
                and    hoi.org_information_context = 'Accounting Information'
    );
           

    --
        -- Now for lot cost method, update gmf_lot_cost_adjustmets table
        -- For Actual/Standard methods, update gmf_period_balances table.
        --
       

    UPDATE gmf_lot_cost_adjustments
    SET   gl_posted_ind      = 0               
    WHERE adjustment_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND   adjustment_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND   legal_entity_id  = '&LE_ID';

    --
    -- Update transaction valuation table
    --

    UPDATE gmf_period_balances
    SET    costed_flag           = 'D'
    WHERE  period_balance_id IN
        (SELECT xte.SOURCE_ID_INT_1
            FROM xla.xla_transaction_entities xte,
                 xla_events xe,
                 gmf_xla_extract_headers geh
            WHERE xte.entity_id       = xe.entity_id
            AND xe.event_id           = geh.event_id
            AND xe.application_id     = 555
            AND geh.transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
            AND geh.transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
            AND geh.event_type_code   = 'COSTREVAL'
                        AND geh.legal_entity_id   = '&LE_ID'
        );

    --
    -- Update Actual Cost Adjustments
    --
               
    UPDATE cm_adjs_dtl
    SET   gl_posted_ind      = 0
    WHERE adjustment_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND   adjustment_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND   organization_id in (
        select hoi.organization_id
                from   hr_organization_information hoi
                where  hoi.org_information2 = '&LE_ID'
                and    hoi.org_information_context = 'Accounting Information'
    );
             
         
    --
    -- Update Cost Allocations
    --
          
               
    UPDATE gl_aloc_dtl
    SET   gl_posted_ind      = 0
    WHERE allocdtl_id IN
        (    SELECT xte.SOURCE_ID_INT_1
            FROM xla.xla_transaction_entities xte,
                 xla_events xe,
                 gmf_xla_extract_headers geh
            WHERE xte.entity_id = xe.entity_id
            AND xe.event_id           = geh.event_id
            AND xe.application_id     = 555
            AND geh.transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
            AND geh.transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
            AND geh.event_type_code   = 'GLCOSTALOC'
            and geh.legal_entity_id   = '&LE_ID'
        );


     /* Start changes V2 Dt. 01-Jul-2015 */
     /* Applicable for OPM-LCM integration for release 12.1 and later */
     /* Start Update OPM-LCM integration tables */
                
         UPDATE gmf_lc_actual_cost_adjs
         SET    accounted_flag     = 'N',
                final_posting_date = NULL
         WHERE  adj_transaction_id IN
                  (
                    SELECT adj_transaction_id
                    FROM   gmf_lc_adj_transactions
                    WHERE  legal_entity_id   = '&LE_ID'
                AND    transaction_date >= TO_DATE('05/07/09 00:00:00','dd/mm/yy hh24:mi:ss')
                AND    transaction_date <= TO_DATE('01/08/09 23:59:59','dd/mm/yy hh24:mi:ss')
                  );
 
         UPDATE gmf_lc_lot_cost_adjs
         SET    accounted_flag     = 'N',
                final_posting_date = NULL
         WHERE  adj_transaction_id IN
                  (
                    SELECT adj_transaction_id
                    FROM   gmf_lc_adj_transactions
                    WHERE  legal_entity_id   = '&LE_ID'
                AND    transaction_date >= TO_DATE('05/07/09 00:00:00','dd/mm/yy hh24:mi:ss')
                AND    transaction_date <= TO_DATE('01/08/09 23:59:59','dd/mm/yy hh24:mi:ss')
                  );

         UPDATE gmf_lc_adj_transactions
         SET    accounted_flag    = 'D'
         WHERE  legal_entity_id   = '&LE_ID'
     AND    transaction_date >= TO_DATE('05/07/09 00:00:00','dd/mm/yy hh24:mi:ss')
    AND    transaction_date <= TO_DATE('01/08/09 23:59:59','dd/mm/yy hh24:mi:ss');

     /* End Update OPM-LCM integration tables */

     /* End changes V2 Dt. 01-Jul-2015 */
    
    --
    -- Delete transaction valuation rows
    --
       
    DELETE FROM gmf_transaction_valuation
    WHERE  transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
    AND    transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
    AND    legal_entity_id   = '&LE_ID';

     create table xla_events_bck as
     select xe.*
     from   xla_events xe,
            gmf_xla_extract_headers gmf
     where  xe.application_id     = 555
     and    xe.event_id           = gmf.event_id
     and    gmf.transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
     and    gmf.transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
         AND    gmf.legal_entity_id   = '&LE_ID';
   
     create table xla_ae_headers_bck as
     select *
     from   xla_ae_headers
     where  application_id = 555
     and    event_id in(select event_id
                        from   xla_events_bck);
   
   
     create table xla_ae_lines_bck as
     select *
     from   xla_ae_lines
     where  application_id = 555
     and    ae_header_id in(select ae_header_id 
                            from   xla_ae_headers_bck);
   
     create table xla_dist_link_bck as
     select *
     from   xla_distribution_links
     where  application_id = 555
     and    ae_header_id in(select ae_header_id 
                            from   xla_ae_headers_bck);
   
   
     create table xla_ae_seg_val_bck as
     select *
     from   xla_ae_segment_values
     where  ae_header_id in(select ae_header_id 
                            from   xla_ae_headers_bck );

    /* Start changes V2 Dt. 01-Jul-2015 */                           
        CREATE TABLE xla_trans_enti_upg_bck AS
        SELECT *
        FROM XLA_TRANSACTION_ENTITIES_UPG
        WHERE application_id = 555
        AND LEGAL_ENTITY_ID  = '&LE_ID'
        AND ENTITY_ID       IN
          ( SELECT ENTITY_ID FROM XLA_AE_HEADERS_BCK
          );                           
    /* End changes V2 Dt. 01-Jul-2015 */

     -- delete data from xla tables
     delete from xla_distribution_links
     where  application_id = 555
     and    ae_header_id in(select distinct ae_header_id
                            from   xla_dist_link_bck);
   
   
     delete from xla_ae_lines
     where  application_id = 555
     and    ae_header_id in(select distinct ae_header_id
                            from   xla_ae_lines_bck);
   
   
     --This one may take sometime.
     delete from xla_ae_segment_values
     where  ae_header_id in(select distinct ae_header_id
                            from   xla_ae_seg_val_bck);
   
   
     delete from xla_ae_headers
     where  application_id = 555
     and    ae_header_id in (select ae_header_id
                             from   xla_ae_headers_bck);
                            
     delete from xla_events
     where  application_id = 555
         and    event_id in(select event_id
                    from   xla_events_bck);
        
         /* Start changes V2 Dt. 01-Jul-2015 */
        DELETE
        FROM XLA_TRANSACTION_ENTITIES_UPG
        WHERE APPLICATION_ID=555
        AND LEGAL_ENTITY_ID = '&LE_ID'
        AND ENTITY_ID      IN
          ( SELECT ENTITY_ID FROM XLA_AE_HEADERS_BCK
          );                
     /* End changes V2 Dt. 01-Jul-2015 */
   
        --
        -- Delete extract lines
        --
        DELETE FROM gmf_xla_extract_lines
        WHERE header_id IN (
            SELECT header_id
            FROM  gmf_xla_extract_headers
            WHERE transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
            AND   transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
                        AND   legal_entity_id   = '&LE_ID'
                );
       
        --
        -- Delete extract headers
        --
   
        DELETE FROM gmf_xla_extract_headers
        WHERE transaction_date >= TO_DATE('01/04/12 00:00:00','dd/mm/yy hh24:mi:ss')
            AND   transaction_date <= TO_DATE('30/04/12 23:59:59','dd/mm/yy hh24:mi:ss')
                AND   legal_entity_id   = '&LE_ID';

Inter-Org Profit

select SEGMENT1, rcvOU,SENDOU, sum(ACCOUNTED_DR) ACCOUNTED_DR, sum(ACCOUNTED_CR) ACCOUNTED_CR, (sum(ACCOUNTED_DR) - sum(ACCOUNTED_CR)) total
from
(
select c.SEGMENT1, a.AE_HEADER_ID,a.ENTITY_ID,b.CODE_COMBINATION_ID,b.ACCOUNTED_DR,b.ACCOUNTED_CR,d.SOURCE_ID_INT_1, u1.NAME rcvOU, e.ORGANIZATION_ID,f.ORGANIZATION_NAME RCVORGNM,f.ORGANIZATION_CODE RCVDORG,
u2.NAME SENDOU, e.TRANSFER_ORGANIZATION_ID,g.ORGANIZATION_NAME SENDRORGNM,g.ORGANIZATION_CODE SENDRORG
,  c.segment6
from xla_ae_headers a,xla_ae_lines b,gl_code_combinations c,xla.xla_transaction_entities d,mtl_material_transactions e,
org_organization_definitions f,org_organization_definitions g, hr_operating_units u1, hr_operating_units u2
where a.PERIOD_NAME = 'Nov-16-17' and a.APPLICATION_ID = 555 and a.AE_HEADER_ID = b.AE_HEADER_ID and a.APPLICATION_ID = b.APPLICATION_ID
and b.CODE_COMBINATION_ID = c.CODE_COMBINATION_ID --and c.SEGMENT1 = '102'
and c.segment6 = '318201'
and a.ENTITY_ID = d.ENTITY_ID and a.APPLICATION_ID = d.APPLICATION_ID and d.SOURCE_ID_INT_1 = e.TRANSACTION_ID and e.ORGANIZATION_ID = f.ORGANIZATION_ID
and e.TRANSFER_ORGANIZATION_ID = g.ORGANIZATION_ID
and f.OPERATING_UNIT=u1.ORGANIZATION_ID
and g.OPERATING_UNIT=u2.ORGANIZATION_ID
--and u2.NAME='PFL-HIP OU'
)
group by SEGMENT1, rcvOU,SENDOU
order by segment1

COGS

select ou,item, itemname,
-- rcvOU,SENDOU,
sum(nvl(ACCOUNTED_DR,0)) ACCOUNTED_DR, sum(nvl(ACCOUNTED_CR,0)) ACCOUNTED_CR, (sum(nvl(ACCOUNTED_DR,0)) - sum(nvl(ACCOUNTED_CR,0))) total
from
(
select c.SEGMENT1 ou,i.SEGMENT1 item, i.DESCRIPTION itemname, a.AE_HEADER_ID,a.ENTITY_ID,b.CODE_COMBINATION_ID,b.ACCOUNTED_DR,b.ACCOUNTED_CR,d.SOURCE_ID_INT_1, u1.NAME rcvOU,
e.ORGANIZATION_ID,f.ORGANIZATION_NAME RCVORGNM,f.ORGANIZATION_CODE RCVDORG,
--u2.NAME SENDOU,
 e.TRANSFER_ORGANIZATION_ID--,g.ORGANIZATION_NAME SENDRORGNM,g.ORGANIZATION_CODE SENDRORG
--,  c.segment6
from xla_ae_headers a,xla_ae_lines b,gl_code_combinations c,xla.xla_transaction_entities d,mtl_material_transactions e,mtl_system_items_b i
,org_organization_definitions f--,org_organization_definitions g
, hr_operating_units u1--, hr_operating_units u2
where a.PERIOD_NAME = 'Nov-16-17' and a.APPLICATION_ID = 555 and a.AE_HEADER_ID = b.AE_HEADER_ID and a.APPLICATION_ID = b.APPLICATION_ID
and b.CODE_COMBINATION_ID = c.CODE_COMBINATION_ID and c.SEGMENT1 ='105'--:p_ou
and c.segment6 = '124818'  -- COGS Account
and a.ENTITY_ID = d.ENTITY_ID and a.APPLICATION_ID = d.APPLICATION_ID
and d.SOURCE_ID_INT_1 = e.TRANSACTION_ID
and e.ORGANIZATION_ID = f.ORGANIZATION_ID --and i.SEGMENT1='44603'
--and e.TRANSFER_ORGANIZATION_ID = g.ORGANIZATION_ID
and f.OPERATING_UNIT=u1.ORGANIZATION_ID
and e.INVENTORY_ITEM_ID=i.INVENTORY_ITEM_ID and e.ORGANIZATION_ID=i.ORGANIZATION_ID
--and g.OPERATING_UNIT=u2.ORGANIZATION_ID
--and u2.NAME='PFL-HIP OU'
)
group by ou,item, itemname--, rcvOU,SENDOU
--order by segment1

Seeded Procedure and Programs for Landed Cost Adjustments Import Process :

Landed Cost Adjustments Import Process  Program Procedure : 

 

 GMF_LC_ADJ_TRANSACTIONS_PKG.Process_Lc_Adjustments

Also, Find out if the Charges cost will double for Landed Cost Adjustments Import Process
 
1) Select
RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE, count(*)
From gmf_lc_adj_transactions
Group by
RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE
having count(*) > 1;




2) select distinct i(INVENTORY_ITEM_ID) From gmf_lc_adj_transactions where
(RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE) IN
(Select
RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE
From gmf_lc_adj_transactions
Group by
RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE
having count(*) > 1);

OU wise Orgs Lists

select  u.SHORT_CODE, u.NAME, count(o.ORGANIZATION_CODE) --over (partition by u.SHORT_CODE)
orgs_per_ou
from org_organization_definitions o, hr_operating_units u
where o.OPERATING_UNIT=u.ORGANIZATION_ID and o.ORGANIZATION_CODE not in ('FMO')
group by  u.SHORT_CODE, u.NAME
order by u.SHORT_CODE

OU wise Orgs

select o.ORGANIZATION_CODE, o.ORGANIZATION_ID, o.ORGANIZATION_NAME, u.SHORT_CODE, u.NAME, count(o.ORGANIZATION_CODE) over (partition by u.SHORT_CODE) orgs_per_ou
from org_organization_definitions o, hr_operating_units u
where o.OPERATING_UNIT=u.ORGANIZATION_ID and o.ORGANIZATION_CODE not in ('FMO')
order by o.ORGANIZATION_CODE

Time stemp

select fcr.request_id request_id, fcp.concurrent_program_name conc_prog,
fcpt.user_concurrent_program_name user_conc_prog, fcr.argument_text,
fcr.actual_start_date start_date, fcr.actual_completion_date completion_date,
round (fcr.actual_completion_date - fcr.actual_start_date, 5) aa,
substr (numtodsinterval (( fcr.actual_completion_date
- fcr.actual_start_date
), 'day' ), 12, 8 ) DURATION,
fcr.status_code, fcr.completion_text
from fnd_concurrent_programs fcp, fnd_concurrent_programs_tl fcpt,
fnd_concurrent_requests fcr
where fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcpt.concurrent_program_id
and fcr.program_application_id = fcpt.application_id
--and fcp.concurrent_program_name = 'GMFACOST'-- '&EnterShortname'   --For example 'GMFACOST'
and rownum < 100
order by trunc ( ( (fcr.actual_completion_date - fcr.actual_start_date) / (1 / 24) ) * 60 ) desc

RA Data Fix

RA Data Fix

SELECT event_id
  FROM xla_events
WHERE 1 = 1
  AND application_id = 222
  AND event_status_code NOT IN('P','N','Z')
  AND event_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY');
 
 
  SELECT *--event_id
       FROM ar_receivable_applications_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3  -- 3 22 652 rows data returns
    
    
      SELECT event_id
       FROM ar_cash_receipt_history_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3;  -- No data return
   
 
  --3rd
  
  SELECT event_id
      FROM ra_cust_trx_line_gl_dist_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3  -- 9 rows return
    
    
      UPDATE ar_receivable_applications_all
     SET posting_control_id = -9999,
         gl_posted_date = TO_DATE('01-OCT-1816','DD-MON-YYYY')
  WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3;
    
    
    
     select * from  ra_cust_trx_line_gl_dist_all
     --SET posting_control_id = -9999,
         --gl_posted_date = TO_DATE('01-OCT-1816','DD-MON-YYYY')
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-1816','DD-MON-YYYY') TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3;
    
    
     SELECT event_id,posting_control_id,gl_posted_date
      FROM ra_cust_trx_line_gl_dist_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
    
    
     SELECT event_id
  FROM xla_events
WHERE 1 = 1
  AND application_id = 222
  AND event_status_code NOT IN('P','N','Z')
  AND event_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY');
 
 
 
     SELECT event_id
  FROM xla_events
WHERE 1 = 1
  AND application_id = 222
  AND event_status_code NOT IN('P','N','Z')
  AND event_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
  -- No data
 

 SELECT event_id
       FROM ar_receivable_applications_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
  -- 38 rows
 
 
  SELECT event_id,gl_posted_date
       FROM ar_cash_receipt_history_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
     -- no data
    
    
     SELECT event_id,posting_control_id,gl_posted_date
      FROM ra_cust_trx_line_gl_dist_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
     -- 7 rows
    
    
     ---- update
    
      UPDATE ar_receivable_applications_all
     SET posting_control_id = -9999,
         gl_posted_date = TO_DATE('01-OCT-1816','DD-MON-YYYY')
  WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
    
     UPDATE ra_cust_trx_line_gl_dist_all
     SET posting_control_id = -9999,
         gl_posted_date = TO_DATE('01-OCT-1816','DD-MON-YYYY')
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;

INV - New Item Creation

CREATE TABLE APPS.XX_ITEM_INTERFACE_STG_DEBUG ( SEGMENT1 VARCHAR2(40 BYTE), DESCRIPTION VARCHAR2(240 BYTE), PRIMARY_UOM_CODE VARCHAR2(3 BYTE), PROCESS_QUALITY_ENABLED_FLAG VARCHAR2(1 BYTE), PROCESS_COSTING_ENABLED_FLAG VARCHAR2(1 BYTE), RECIPE_ENABLED_FLAG VARCHAR2(1 BYTE), PROCESS_EXECUTION_ENABLED_FLAG VARCHAR2(1 BYTE), I_STSTAUS VARCHAR2(500 BYTE), LONG_DESC VARCHAR2(4000 BYTE) ) ------------------------------------------------------------

 

 

 Insert into XX_ITEM_INTERFACE_STG_DEBUG select item_id, item_name, 'DZ' unit , 'Y','Y','Y','Y', null,null from item_master@LSALES a, rfl_fg_item_list b, ITEM_DIST_UNITS@LSALES ut where b.ITEM_CODE=a.item_id and a.ITEM_D_UNITS_ID=ut.ITEM_D_UNITS_ID and upper(ITEM_D_UNITS_NAME)='DOZEN' --872038 and not exists (select * from mtl_system_items_b itm where a.item_id= itm.segment1 and itm.ORGANIZATION_ID=102) ------------------------------------------------------------- -- /* Cursor Declaration ***/ declare cursor c1 is --select * from XX_ITEM_INTERFACE_STG_DEBUG where SEGMENT1 in ('911461','911462'); --xx_item_interface_stg1; select a.* from XX_ITEM_INTERFACE_STG_DEBUG a where -- a.SEGMENT1 in ('911461','911462') and a.SEGMENT1 not in (select segment1 from mtl_system_items_b i where organization_id=102 and a.SEGMENT1= i.SEGMENT1 ) -- and a.SEGMENT1 in ('911465','911466','911467') ; --select ATTRIBUTE1, I.* from mtl_system_items_b i where organization_id=102 and i.SEGMENT1 in ('32144') /* Variable Declaration **/ l_org_id number(3); l_uom varchar2(15); l_suom varchar2(15); l_item_type varchar2(60); l_desc varchar2(60); l_temp_id varchar2(3); l_sale_ac number(16); l_exp_ac number(16); l_cost_sale_ac number(16); l_item_number varchar2(100); l_flag varchar2(4) default'A'; l_msg varchar2(200); begin --delete from MTL_SYSTEM_ITEMS_INTERFACE; --commit; for x1 in c1 loop /* Validation For Organization */ -- begin -- select organization_id into l_org_id -- from ORG_ORGANIZATION_DEFINITIONS -- where organization_code = x1.organization_code; -- exception -- when others then -- l_flag :='E'; -- l_msg :='Item Interface'||'Organization Id'||l_org_id||' not in system'; -- fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); -- end; /* Validation for Item Number */ if x1.SEGMENT1 is null then l_flag :='E'; l_msg :='Item number should not be NULL'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured--'||l_msg); end if; /* Validation for Description */ if x1.DESCRIPTION is null then l_flag :='E'; l_msg :='Item description should not be NULL'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg|| x1.SEGMENT1); end if; /* Validation for Unit Of Measure */ if x1.PRIMARY_UOM_CODE is not null then begin select UOM_CODE into l_uom from mtl_units_of_measure where UOM_CODE =x1.PRIMARY_UOM_CODE; exception when others then l_flag :='E'; l_msg := x1.SEGMENT1||'---'||l_uom ||'UOM is not Valid'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end; else l_msg :='Primary UOM should not be NULL '; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end if; begin select COST_OF_SALES_ACCOUNT into l_cost_sale_ac from MTL_PARAMETERS where ORGANIZATION_ID = 102; exception when no_data_found then l_flag :='E'; l_msg :='Cost of Sales Account should not be Null'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); when others then l_flag :='E'; l_msg :='Cost of Sales Account is not Valid'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end; begin select SALES_ACCOUNT into l_sale_ac from MTL_PARAMETERS where ORGANIZATION_ID = 102; exception when no_data_found then l_flag :='E'; l_msg :='Sales Account should not be Null'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); when others then l_flag :='E'; l_msg :='Sales Account is not Valid'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end; begin select EXPENSE_ACCOUNT into l_exp_ac from MTL_PARAMETERS where ORGANIZATION_ID = 102; exception when no_data_found then l_flag :='E'; l_msg :='Expence Account should not be Null'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); when others then l_flag :='E'; l_msg :='Expence Account is not Valid'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end; if l_flag!='E' then insert into MTL_SYSTEM_ITEMS_INTERFACE ( ORGANIZATION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION, SEGMENT1, PRIMARY_UOM_CODE, TEMPLATE_ID, SUMMARY_FLAG, ENABLED_FLAG, SALES_ACCOUNT, COST_OF_SALES_ACCOUNT, EXPENSE_ACCOUNT, PROCESS_QUALITY_ENABLED_FLAG, PROCESS_COSTING_ENABLED_FLAG, RECIPE_ENABLED_FLAG, PROCESS_EXECUTION_ENABLED_FLAG, RESTRICT_SUBINVENTORIES_CODE, RESTRICT_LOCATORS_CODE , LOCATION_CONTROL_CODE , SET_PROCESS_ID, PROCESS_FLAG, TRANSACTION_TYPE/*, ATTRIBUTE1, -- ou ATTRIBUTE25, -- TF ATTRIBUTE26, -- unload ATTRIBUTE27, -- load ATTRIBUTE28, -- DP ATTRIBUTE29, -- TP ATTRIBUTE30 -- MRP*/ ) values( 102, sysdate, fnd_global.USER_ID, sysdate, fnd_global.USER_ID, fnd_global.LOGIN_ID, x1.DESCRIPTION, x1.SEGMENT1, l_uom, -- l_item_type, -- 1, -- 1 for FG item, 2 for Purchase 1, 'Y', 'Y', l_sale_ac, l_cost_sale_ac, l_exp_ac, x1.PROCESS_QUALITY_ENABLED_FLAG, x1.PROCESS_COSTING_ENABLED_FLAG, x1.RECIPE_ENABLED_FLAG, x1.PROCESS_EXECUTION_ENABLED_FLAG, 1, 1, 2, 2, 1, 'CREATE'/*, x1.ATTRIBUTE1, x1.ATTRIBUTE25, -- TF x1.ATTRIBUTE29, -- unload x1.ATTRIBUTE30, -- load x1.ATTRIBUTE27, -- DP x1.ATTRIBUTE28, -- TP x1.ATTRIBUTE26 -- MRP*/ ); -- MTL_INTERFACE_ERRORS -- update XX_ITEM_INTERFACE_STG_DEBUG set I_STATUSES='OK' where SEGMENT1= x1.SEGMENT1; -- commit; else update XX_ITEM_INTERFACE_STG_DEBUG set I_STSTAUS=l_msg where SEGMENT1= x1.SEGMENT1; commit; end if; end loop; commit; end; / ------------------------------------------------------------

Seeded Procedure for GMF Recreate Batch / Period Layers

 

GMF Recreate Batch / Period Layers:  GMF_LAYERS.RELAYER

URL From Toad

URL From Toad

    
    SELECT home_url FROM icx_parameters;

Errors: EAM

Errors: Create Accounting

Error : Intercompany balancing was unable to generate the intercompany account.  Please check the intercompany balancing rules setup.

Solution :
select  distinct count(ORGANIZATION_CODE) times,ORGANIZATION_id, ORGANIZATION_CODE, o.ORGANIZATION_NAME, er.ENCODED_MSG, xah.AE_HEADER_ID, xte.ENTITY_ID, SOURCE_ID_INT_1
from xla_accounting_errors er, xla_ae_headers xah, xla_ae_lines xal,xla.xla_transaction_entities xte,
org_organization_definitions o
where er.APPLICATION_ID=555 and
er.APPLICATION_ID=xah.APPLICATION_ID
and er.ENTITY_ID=xah.ENTITY_ID and er.REQUEST_ID=xah.REQUEST_ID and
er.REQUEST_ID=:P_REQUEST_ID --2056025
and xah.APPLICATION_ID=xal.APPLICATION_ID and xah.APPLICATION_ID=555
and xah.AE_HEADER_ID=xal.AE_HEADER_ID
and trunc(xah.CREATION_DATE)=trunc(xal.CREATION_DATE)
and xah.REQUEST_ID=xal.REQUEST_ID and xah.REQUEST_ID=:P_REQUEST_ID
and xah.PROGRAM_APPLICATION_ID=xal.PROGRAM_APPLICATION_ID and o.ORGANIZATION_CODE='478'
and er.ENTITY_ID=xte.ENTITY_ID and er.APPLICATION_ID=xte.APPLICATION_ID
and xte.SECURITY_ID_INT_1=o.ORGANIZATION_ID
GROUP BY ORGANIZATION_CODE, ORGANIZATION_id, o.ORGANIZATION_NAME , er.ENCODED_MSG, xah.AE_HEADER_ID, xte.ENTITY_ID, SOURCE_ID_INT_1


SOURCE_ID_INT_1=TRANSACTION_ID

select DISTRIBUTION_ACCOUNT_ID from mtl_material_transactions t where t.TRANSACTION_ID=120481385

select * from gl_code_combinations c where c.CODE_COMBINATION_ID=825229 

Check the Account Code is Active or Not. If inactive Active it then run Create Accounting program.


SLA Error check with Transaction  :

select distinct --DISTRIBUTION_ACCOUNT_ID,
c.SEGMENT1 ||' - '|| fv1.DESCRIPTION segment1,
 c.SEGMENT6||' - '|| fv.DESCRIPTION segment6 from mtl_material_transactions t, gl_code_combinations c, FND_FLEX_VALUES_VL FV, FND_FLEX_VALUES_VL FV1  where t.DISTRIBUTION_ACCOUNT_ID=c.CODE_COMBINATION_ID
 and c.SEGMENT6=fv.FLEX_VALUE and c.SEGMENT1=fv1.FLEX_VALUE
 and t.TRANSACTION_ID in (
select  distinct
--count(ORGANIZATION_CODE) times,ORGANIZATION_id, ORGANIZATION_CODE, o.ORGANIZATION_NAME, er.ENCODED_MSG, xah.AE_HEADER_ID, xte.ENTITY_ID,
 SOURCE_ID_INT_1
from xla_accounting_errors er, xla_ae_headers xah, xla_ae_lines xal,xla.xla_transaction_entities xte,
org_organization_definitions o
where er.APPLICATION_ID=555 and
er.APPLICATION_ID=xah.APPLICATION_ID
and er.ENTITY_ID=xah.ENTITY_ID and er.REQUEST_ID=xah.REQUEST_ID and
er.REQUEST_ID=:P_REQUEST_ID --2056025
and xah.APPLICATION_ID=xal.APPLICATION_ID and xah.APPLICATION_ID=555
and xah.AE_HEADER_ID=xal.AE_HEADER_ID
and trunc(xah.CREATION_DATE)=trunc(xal.CREATION_DATE)
and xah.REQUEST_ID=xal.REQUEST_ID and xah.REQUEST_ID=:P_REQUEST_ID
and xah.PROGRAM_APPLICATION_ID=xal.PROGRAM_APPLICATION_ID-- and o.ORGANIZATION_CODE='478'
and er.ENTITY_ID=xte.ENTITY_ID and er.APPLICATION_ID=xte.APPLICATION_ID
and xte.SECURITY_ID_INT_1=o.ORGANIZATION_ID
GROUP BY ORGANIZATION_CODE, ORGANIZATION_id, o.ORGANIZATION_NAME , er.ENCODED_MSG, xah.AE_HEADER_ID, xte.ENTITY_ID, SOURCE_ID_INT_1
)

Errors: Accounting Preprocessor

Error Check after run pre-processor:

 
Select * from GL_MESG_TBL pe where pe.request_id= :request_id

Errors: Actual Cost Process

Errors: XLA

Errors: SLA

Errors: Internal location is Already assigned to another Ship-to Address

 If it returns any row, it means that, indeed, an Internal location is Already assigned to another Ship-to Address 

SELECT * FROM PO_LOCATION_ASSOCIATIONS_ALL WHERE LOCATION_ID = &location_id AND ORG_ID = &enter org_id;

 

For deletion of internal location association

Please run following datafix:

Take a backup of table :- FROM PO_LOCATION_ASSOCIATIONS_ALL

 DELETE FROM PO_LOCATION_ASSOCIATIONS_ALL  WHERE LOCATION_ID = &location_id AND ORG_ID = &enter org_id;

Errors: GL

Errors: AP

Errors: OPM

Item Reserved till now and on-hand quantity allocated but no record found from Batch 

select *
FROM apps.mtl_reservations a, apps.gme_batch_header b, apps.mtl_parameters c, apps.gme_material_details d
WHERE --demand_source_type_id=5 AND supply_source_type_id=13
 a.demand_source_header_id=b.batch_id
AND a.organization_id=c.organization_id
AND a.organization_id=b.organization_id
AND b.batch_id=d.batch_id
AND a.demand_source_line_id=d.material_detail_id
--and  b.ORGANIZATION_ID=204
and a.INVENTORY_ITEM_ID=69106

Errors : Landed Cost Adjustments Import Process eBS R12

To run : Landed Cost Adjustments Import Process

Error : 

01. ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Action :

Check data by SQL: 

select * from PON_PRICE_ELEMENT_TYPES_TL where length(name) > 25;

select price_element_code from PON_PRICE_ELEMENT_TYPES where PRICE_ELEMENT_TYPE_ID in
(select PRICE_ELEMENT_TYPE_ID from PON_PRICE_ELEMENT_TYPES_TL where length(name) > 25); 

if exists then follow the solution:

Solution :

Please run the following script on the TEST instance:

update PON_PRICE_ELEMENT_TYPES_TL set name = substr(name, 0, 30) where length(name) > 30;
commit;

After this verify the outcome of the update using the following query:

select * from PON_PRICE_ELEMENT_TYPES_TL where length(name) > 30;

After this run the Landed Cost Adjustments Import Process request and see if it still completes in error.

Error :
02. Input parameters Legal entity / Id PFL_LE/23275 Cost component class ID null for Charge type component The line was not processed Rcv Ttransaction ID: 817450 Ship Num: 0000020015020210 Ship Line Num: 1 Item ID: 9781 Organization ID: 263 Adjustment Num: 0

Action :

Check data by SQL:  

 select PRICE_ELEMENT_TYPE_ID, PRICE_ELEMENT_CODE,  COST_COMPONENT_CLASS_ID,COST_ANALYSIS_CODE  from PON_PRICE_ELEMENT_TYPES t
 where COST_COMPONENT_CLASS_ID is null or COST_ANALYSIS_CODE is null

Find the Last one which you have updated or included or last time you have used in your application.

Solution :

Add cost component class and analyses code on all cost factors that are used for LCM charges.
Navigation Path Purchasing Super User (process Operations) responsibility, Setup – Purchasing – Cost Factors

2. Re-run the Landed Cost Adjustments Import Process

Supplier Opening Balance Upload( Invoice/Prepayment) in Ebs R12

delete from ap_invoice_lines_interface where org_id='148' and description ='IOU Opening Balance upto 31-OCT-2016';
delete from ap_invoices_interface  where org_id='148' and INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT';
delete from ap_interface_rejections;
--delete from pran_ap_invoices_stg;
commit;


-- balance upload

insert into MPFL_ap_invoices_stg   (OU_NAME, ORG_ID, INVOICE_TYPE, VENDOR_NAME, VENDOR_NUMBER, VENDOR_SITE, INVOICE_CURRENCY, EXCHANGE_RATE, INVOICE_NO, INVOICE_DATE, INVOICE_AMOUNT, DESCRIPTION, GL_DATE, PAYMENT_METHOD_CODE, LINE_NUMBER,
    LINE_TYPE, LINE_AMOUNT, CODE_COMBINATION, H_ERROR_MESSAGE, H_VERIFY_FLAG,     L_ERROR_MESSAGE, L_VERIFY_FLAG, INVOICE_AMOUNT2, INVOICE_AMOUNT3) Values('Selling OU',null,'PREPAYMENT','IOU : Md.Shafiqul Islam Khan (12309)','12912309',Null,'BDT',1,'SA/OPBAL/12912309','31-OCT-2016',6382600,'IOU Opening Balance upto 31-OCT-2016','31-OCT-2016','CHECK',1,'Item', 6382600, '129-000-000-0000-00000-124701-000-000-00000-00000', NULL, null, NULL,null, 6382600, 0);
insert into MPFL_ap_invoices_stg   (OU_NAME, ORG_ID, INVOICE_TYPE, VENDOR_NAME, VENDOR_NUMBER, VENDOR_SITE, INVOICE_CURRENCY, EXCHANGE_RATE, INVOICE_NO, INVOICE_DATE, INVOICE_AMOUNT, DESCRIPTION, GL_DATE, PAYMENT_METHOD_CODE, LINE_NUMBER,
    LINE_TYPE, LINE_AMOUNT, CODE_COMBINATION, H_ERROR_MESSAGE, H_VERIFY_FLAG,     L_ERROR_MESSAGE, L_VERIFY_FLAG, INVOICE_AMOUNT2, INVOICE_AMOUNT3) Values('Selling OU',null,'PREPAYMENT','IOU : Khandoker Abdul Wadud (15438)','12915438',Null,'BDT',1,'SA/OPBAL/12915438','31-OCT-2016',399481,'IOU Opening Balance upto 31-OCT-2016','31-OCT-2016','CHECK',1,'Item', 399481, '129-000-000-0000-00000-124701-000-000-00000-00000', NULL, null, NULL,null, 399481, 0);



exec APPS.MPFL_ap_invoice_api();

update ap_invoices_interface set TERMS_ID = 10000, PAYMENT_METHOD_LOOKUP_CODE = 'CHECK',
TERMS_DATE='31-OCT-2016',PAYMENT_METHOD_CODE='CHECK' where org_id='148' and INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT';


commit;


CREATE OR REPLACE procedure APPS.MPFL_ap_invoice_api
as

l_verify_flag char(1);
l_org_id hr_operating_units.organization_id%type;
l_error_message varchar2(3000);
l_invoice_type ap_lookup_codes.lookup_code%type;
l_vendor_id po_vendors.vendor_id%type ;
l_vendor_site_id po_vendor_sites_all.vendor_site_id%type;
l_line_type ap_invoice_lines_interface.line_type_lookup_code%type;
l_currency_code fnd_currencies.currency_code%type ;
l_code_combination_id number(20);


cursor h_invoice is
select distinct
ou_name,
invoice_type,
invoice_no,
vendor_number,
vendor_name,
vendor_site,
invoice_currency,
invoice_date,
invoice_amount,
description,
gl_date,
exchange_rate
from MPFL_ap_invoices_stg
where nvl(h_verify_flag,'N') = 'N';


cursor l_invoice (p_invoice_no varchar2) is
select distinct invoice_no,
description,
line_type,
code_combination,
line_number,
line_amount
from MPFL_ap_invoices_stg
where invoice_no = p_invoice_no
and nvl(l_verify_flag,'N') = 'N';



begin


for h1 in h_invoice loop

l_verify_flag := 'Y';
l_error_message := null;



begin
select organization_id
into l_org_id
from hr_operating_units
where upper(name) = upper(trim(h1.ou_name));

exception
when others then
l_verify_flag := 'N';
l_error_message := 'Invalid Operating Unit...';
end;


begin

select lookup_code
into l_invoice_type
from ap_lookup_codes
where lookup_type(+) = 'INVOICE TYPE'
and upper(displayed_field) = upper(trim(h1.invoice_type));

exception
when others then
l_verify_flag := 'N';
l_error_message := 'Invalid Invoice Type...';
end;


begin

select
pv.vendor_id,
pvs.vendor_site_id
into l_vendor_id,
l_vendor_site_id
from po_vendors pv,
po_vendor_sites_all pvs
where --upper(pv.vendor_name) = upper(trim(h1.vendor_name)) SEGMENT1
upper(pv.segment1) = upper(trim(h1.vendor_number))
and pv.vendor_id = pvs.vendor_id
--and pvs.vendor_site_code = h1.vendor_site
and pvs.ORG_ID = l_org_id; --h1.org_id;

Exception
when no_data_found then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Vendor or Vendor Site not found...' ;

when too_many_rows then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Vendor or Vendor Site is more than one...' ;

when others then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Vendor or Vendor Site has other errors' ;

end;


begin

select currency_code
into l_currency_code
from fnd_currencies
where currency_code = h1.invoice_currency;

Exception
when no_data_found then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Currency Code not found...' ;

when too_many_rows then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Currency Code is more than one...' ;

when others then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Currency code has other errors' ;
end;



If (h1.invoice_no is null or h1.invoice_date is null or h1.invoice_amount is null or h1.gl_date is null) then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Invoice No or Invoice Date or Invoice Amount or GL Date is Missing...' ;
end if;



if l_verify_flag <> 'N' then
--
-- inserting into ap_invoices_interface table
--
begin

insert into
ap_invoices_interface( invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
description,
source,
gl_date,
org_id,
exchange_rate,
goods_received_date,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
group_id ---To restrict the import
) values ( ap_invoices_interface_s.nextval,
h1.Invoice_No,
l_invoice_type,
h1.invoice_date,
l_vendor_id,
l_vendor_site_id,
h1.invoice_amount,
l_currency_code,
h1.description,
'MANUAL INVOICE ENTRY',
h1.gl_date,
l_org_id,
h1.exchange_rate,
h1.invoice_date-2,
sysdate,
1110,
sysdate,
1110,
'MIGRATE' );

update MPFL_ap_invoices_stg
set h_verify_flag = 'Y'
where invoice_no = h1.invoice_no;
commit;

exception
when others then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Data not migrated into ap_invoices_interface table...' ;
update MPFL_ap_invoices_stg
set h_verify_flag = 'N',
h_error_message = l_error_message
where invoice_no = h1.invoice_no;
end;


--
-- lines data inserting into ap_invoice_lines_interface
--

for l1 in l_invoice (h1.invoice_no)
loop


begin

select lookup_code
into l_line_type
from ap_lookup_codes
where lookup_type(+) = 'INVOICE DISTRIBUTION TYPE'
and displayed_field = l1.line_type;

exception
when others then
l_verify_flag := 'N';
l_error_message := 'Line type is not valid...';

end;


begin

select code_combination_id
into l_code_combination_id
from gl_code_combinations_v
where segment1||'-'||segment2||'-'||segment3||'-'||segment4||'-'||segment5||'-'||segment6||'-'||segment7||'-'||segment8||'-'||segment9||'-'||segment10 = l1.code_combination;

exception
when others then
l_verify_flag := 'N';
l_error_message := 'Code Combination is not valid...';
end;

if l_verify_flag <> 'N' then

begin

insert into
ap_invoice_lines_interface ( invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
dist_code_combination_id,
org_id,
description
)values( ap_invoices_interface_s.currval,
ap_invoice_lines_interface_s.nextval,
nvl(l1.line_number,1),
l_line_type,
l1.line_amount,
l_code_combination_id,
l_org_id,
l1.description);

update MPFL_ap_invoices_stg
set l_verify_flag = 'Y'
where invoice_no = l1.invoice_no
and line_number = l1.line_number;


exception

when others then
l_error_message := l_error_message || sqlerrm;
l_verify_flag := 'N';
update MPFL_ap_invoices_stg
set l_verify_flag = 'N',
l_error_message = l_error_message
where invoice_no = l1.invoice_no
and line_number = l1.line_number;

commit;

end;

else

update MPFL_ap_invoices_stg
set l_error_message = l_error_message,
l_verify_flag = 'N'
where invoice_no = l1.invoice_no
and line_number = l1.line_number;

end if;

end loop; ---end of line loop



else

update MPFL_ap_invoices_stg
set h_error_message = l_error_message,
h_verify_flag = 'N'
where invoice_no = h1.invoice_no;

end if;


end loop; ---end of header loop

commit;

end MPFL_ap_invoice_api;
/

New Supplier Open to Opening Balance upload in EBS R12

CREATE TABLE APPS.MPFL_XXSUPPLIER_STG
(
  CODE            VARCHAR2(40 BYTE),
  SUPPLIER_NAME   VARCHAR2(250 BYTE),
  ADDRESS_LINE1   VARCHAR2(250 BYTE),
  ADDRESS_LINE2   VARCHAR2(250 BYTE),
  ADDRESS_LINE3   VARCHAR2(250 BYTE),
  ADDRESS_LINE4   VARCHAR2(250 BYTE),
  CONTACT_PERSON  VARCHAR2(250 BYTE),
  PHONE           VARCHAR2(40 BYTE),
  FAX             VARCHAR2(40 BYTE),
  SITE_CODE       VARCHAR2(20 BYTE),
  ERROR_MSG       VARCHAR2(250 BYTE)
);

SET DEFINE OFF;
Insert into MPFL_XXSUPPLIER_STG
   (CODE, SUPPLIER_NAME, ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3,
    ADDRESS_LINE4, CONTACT_PERSON, PHONE, FAX, SITE_CODE,
    ERROR_MSG)
 Values
   ('12918603', 'IOU : Md.Babul Hossain (18603)', 'Dhaka', 'HO-Badda', 'Dhaka',
    'Dhaka', 'SA-ADMIN', '01912257303', NULL, 'OFFICE',
    'Opened');
COMMIT;


CREATE OR REPLACE PROCEDURE APPS.MPFL_supplier_api_MA AS
--declare
--cursor c1 is select organization_id from hr_operating_units ;
cursor c2 is select * from MPFL_xxsupplier_stg where ERROR_MSG is null ;

l_vendor_rec                    ap_vendor_pub_pkg.r_vendor_rec_type;
l_vendor_site_rec               ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_vendor_contact_rec            AP_VENDOR_PUB_PKG.R_VENDOR_CONTACT_REC_TYPE;

l_return_status             VARCHAR2(10);
l_msg_count                 NUMBER;
l_msg_data                  VARCHAR2(8000);
l_data                      VARCHAR2 (2000);
l_msg_index                 NUMBER;

l_vendor_id                 NUMBER;
l_party_id                  NUMBER;
l_vendor_site_id            NUMBER;
l_party_site_id             NUMBER;
l_location_id               NUMBER;
x_vendor_contact_id         NUMBER;
x_per_party_id              NUMBER;
x_rel_party_id              NUMBER;
x_rel_id                    NUMBER;
x_org_contact_id            NUMBER;
x_party_site_id             NUMBER;

--l_vendor_name_alt   VARCHAR2(10);--ap_supplier.VENDOR_NAME_ALT%type;

 --ap_supp_rec                   ap.ap_suppliers_int%ROWTYPE;

BEGIN
    delete from xxvendor;
    commit;
   
    FND_GLOBAL.APPS_INITIALIZE(USER_ID=>1110,RESP_ID=>NULL,RESP_APPL_ID=>NULL);
    for x2 in c2 loop
             
        /* Supplier Creation */
        --Required

        l_vendor_rec.vendor_name    := x2.supplier_name; --Supplier Name
        l_vendor_rec.segment1       := x2.code;               -- supplier code
        --Optional
        l_vendor_rec.match_option:='R' ;
        --l_venvod_rec.vendor_name_alt :=x2.supplier_name;
       
        pos_vendor_pub_pkg.create_vendor
            (
                p_vendor_rec    => l_vendor_rec,
                x_return_status => l_return_status,
                x_msg_count     => l_msg_count,
                x_msg_data      => l_msg_data,
                x_vendor_id     => l_vendor_id,
                x_party_id      => l_party_id
            );

        IF l_return_status = fnd_api.g_ret_sts_success  THEN
            fnd_file.put_line (fnd_file.LOG,
                                  ' Vendor Id :'
                               || l_vendor_id
                               || ' Created .'
                              );
            DBMS_OUTPUT.put_line ('Vendor Id :'
                               || l_vendor_id
                               || ' Created '
                               );
         --fnd_file.put_line(fnd_file.log,'return_status: '||l_return_status);
         --fnd_file.put_line(fnd_file.log,'msg_data: '||l_msg_data);
         --DBMS_OUTPUT.put_line ('Vendor Creation: ');
         --DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
         --DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
        -- fnd_file.put_line(fnd_file.log,'Supplier: '||x2.supplier_name);
     
        /* Supplier Site Creation */
       
        --Required
        l_vendor_site_rec.vendor_id :=l_vendor_id ;
        l_vendor_site_rec.vendor_site_code :=x2.site_code ;
        l_vendor_site_rec.address_line1 :=x2.address_line1 ;
        l_vendor_site_rec.country := 'BD';
        l_vendor_site_rec.county := 'Bangladesh';
        l_vendor_site_rec.org_id  := 148;--x1.organization_id ;
       
       

        --Optional
        l_vendor_site_rec.address_line2  :=x2.address_line2;
        l_vendor_site_rec.address_line3  :=x2.address_line3;
        l_vendor_site_rec.ADDRESS_LINE4  :=x2.address_line4;
        l_vendor_site_rec.purchasing_site_flag:='Y';
        l_vendor_site_rec.pay_site_flag :='Y';
        l_vendor_site_rec.rfq_only_site_flag :='N';
       
       -- l_vendor_site_rec.vendor_name_alt               := l_vendor_site_rec.vendor_name || ' ALT';


        pos_vendor_pub_pkg.create_vendor_site
        (
        p_vendor_site_rec => l_vendor_site_rec,
        x_return_status   => l_return_status,
        x_msg_count       => l_msg_count,
        x_msg_data        => l_msg_data,
        x_vendor_site_id  => l_vendor_site_id,
        x_party_site_id   => l_party_site_id,
        x_location_id     => l_location_id
        );
       
        IF l_return_status = fnd_api.g_ret_sts_success  THEN
            fnd_file.put_line (fnd_file.LOG,
                                  ' Vendor Site Id :'
                               || l_vendor_site_id
                               || ' Created .'
                              );
            DBMS_OUTPUT.put_line ('Vendor Site Id  :'
                               || l_vendor_site_id
                               || ' Created '
                               );
       -- fnd_file.put_line(fnd_file.log,'return_status: '||l_return_status);
        --fnd_file.put_line(fnd_file.log,'msg_data: '||l_msg_data);
        --DBMS_OUTPUT.put_line ('Vendor Site Creation: ');
        --DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
        --DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
        
        /*  Supplier Contact Information */
       
          --- Required
        l_vendor_contact_rec.vendor_id              :=l_vendor_id ;
        l_vendor_contact_rec.vendor_site_id         :=l_vendor_site_id;
        l_vendor_contact_rec.party_site_id          :=l_party_site_id;
       -- l_vendor_contact_rec.person_first_name := 'B';
        l_vendor_contact_rec.person_last_name       :=x2.contact_person;
       -- l_vendor_contact_rec.org_id                   := x1.organization_id ;
        --optional
        l_vendor_contact_rec.phone                  :=x2.phone ;
        l_vendor_contact_rec.fax_phone              :=x2.fax;
       
        AP_VENDOR_PUB_PKG.CREATE_VENDOR_CONTACT
        (   p_api_version               => 1.0,
            p_init_msg_list             => fnd_api.g_false,
            p_commit                    => fnd_api.g_false,
            p_validation_level          => FND_API.G_VALID_LEVEL_FULL,
            p_vendor_contact_rec        => l_vendor_contact_rec,
            x_return_status             => l_return_status,
            x_msg_count                 => l_msg_count,
            x_msg_data                  => l_msg_data,
            x_vendor_contact_id         => x_vendor_contact_id,
            x_per_party_id              => x_per_party_id,
            x_rel_party_id              => x_rel_party_id,
            x_rel_id                    => x_rel_id,
            x_org_contact_id            => x_org_contact_id,
            x_party_site_id             => x_party_site_id
        );   
       
        fnd_file.put_line (fnd_file.LOG,
                                  ' Vendor Contact Id :'
                               || x_vendor_contact_id
                               || ' Created .'
                              );
        DBMS_OUTPUT.put_line ('Vendor Contact Id  :'
                               || x_vendor_contact_id
                               || ' Created '
                               );
        --fnd_file.put_line(fnd_file.log,'return_status: '||l_return_status);
        -- fnd_file.put_line(fnd_file.log,'msg_data: '||l_msg_data);
        --DBMS_OUTPUT.put_line ('Vendor Contact Creation: ');
        --DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
        --DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
       
        END IF;
        DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
        DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
           
        insert into xxvendor values(l_vendor_id);
        
        update MPFL_xxsupplier_stg  set ERROR_MSG='Opened' where code = x2.code;
       
        --delete from xxsupplier_stg where code = x2.code and CODE=123456;
      END IF;
      DBMS_OUTPUT.put_line ('return_status : '||l_return_status);
      DBMS_OUTPUT.put_line ('msg_data : '||l_msg_data);
      DBMS_OUTPUT.put_line('Error In Supplier Creation: '||x2.code||substr(l_return_status,1,250));
     
      IF l_return_status <> fnd_api.g_ret_sts_success
         THEN
            FOR i IN 1 .. l_msg_count
            LOOP
               oe_msg_pub.get (p_msg_index          => i,
                               p_encoded            => fnd_api.g_false,
                               p_data               => l_data,
                               p_msg_index_out      => l_msg_index
                              );
               DBMS_OUTPUT.put_line (   'Error In Supplier Creation: '
                                     || x2.code ||l_data
                                    );
            END LOOP;
       END IF;
            
    end loop;   
    COMMIT;   
Exception
      When OTHERS then
        fnd_file.put_line(fnd_file.log,'Error In Supplier Creation: '||substr(l_return_status,1,250));
        fnd_file.put_line(fnd_file.log,RPAD('*',80,'*'));
       -- DBMS_OUTPUT.put_line('Error In Supplier Creation: '||x2.code||substr(l_return_status,1,250));
END;
/

   update ap.ap_suppliers ss set VENDOR_NAME_ALT=VENDOR_NAME, VENDOR_TYPE_LOOKUP_CODE='EMP'
  where exists
  ( select s.VENDOR_ID from ap.ap_suppliers s, apps.mpfl_XXSUPPLIER_STG tm where s.VENDOR_NAME=tm.SUPPLIER_NAME and s.VENDOR_ID=ss.VENDOR_ID
  ) ;
 
exec APPS.MPFL_supplier_api_MA();