Discrete Job Migration Along With PO

Discrete Job Migration Along With PO

Script 

CREATE OR REPLACE PACKAGE BODY
APPS.XXEUR_CREATE_DISCRETE_JOB
AS
PROCEDURE main(errbuf            OUT 
VARCHAR2
              ,retcode           OUT 
NUMBER
              ,p_validate_flag   IN  
VARCHAR2
              ,p_load_flag       IN  
VARCHAR2
              ,p_process_flag    IN  
VARCHAR2
              ,p_pocreate_flag   IN  
VARCHAR2
              )
IS
     
lc_errbuf    VARCHAR2 (500);
     
ln_retcode   NUMBER;
BEGIN
     
fnd_file.put_line (fnd_file.LOG,
     

>>>>>>>>>>>>>>>>>>
Executing main
<<<<<<<<<<<<<<<<<<<<<<<<<‘
     
);
     
fnd_file.put_line (fnd_file.LOG,’ ‘);
  
–======================================
  
Executes validate customer PROCEDURE
  
–======================================
   IF
p_validate_flag = ‘Y’ THEN
     
validate_jobs;
  
END IF;
  
–======================================
  
Execute validated customer PROCEDURE
  
–======================================
   IF
p_load_flag = ‘Y’ THEN
     
fnd_file.put_line (fnd_file.LOG,
     
‘Start Loading data into interface table ‘);
     
load_data;
   END
IF;
   IF
p_process_flag = ‘Y’ THEN
     
fnd_file.put_line (fnd_file.LOG,’Start Creating the jobs’);
     
process_data;
  
fnd_file.put_line (fnd_file.LOG,’End Creating jobs’);
  
END IF;
   
IF p_pocreate_flag = ‘Y’ THEN
      
fnd_file.put_line (fnd_file.LOG,’Start Creating PO’);
      
po_auto_create;
  
fnd_file.put_line (fnd_file.LOG,’End Creating PO’);
  
END IF;
     
fnd_file.put_line (fnd_file.LOG,’ ‘);
     
fnd_file.put_line (fnd_file.LOG,
     

>>>>>>>>>>>>>>>>>>
Executing xxcns_customer_conv_pkg.main – Exit
<<<<<<<<<<<<<<<<<<<<<<<<<‘
     
);
EXCEPTION
  
WHEN OTHERS THEN
     
fnd_file.put_line (fnd_file.LOG,
     
‘Error in xxcns_customer_conv_pkg.main: ‘ || SQLCODE|| ‘ – ‘|| SQLERRM);
END main;
PROCEDURE validate_jobs
IS
— =================
 
Local Variables
 –=================
   ln_count                 NUMBER           := 0;
  
lc_cntry                 VARCHAR2
(50);
  
ln_ou_count              NUMBER;
  
lc_cust_status           VARCHAR2
(200);
  
lc_rec_status            VARCHAR2
(10)    := ‘S’;
  
lc_msg                   VARCHAR2
(10000) := NULL;
  
lc_error_msg             VARCHAR2
(4000)  := ‘Err Msg-VL:-> ‘;
  
lc_territory             VARCHAR2
(50);
  
ln_party_id              NUMBER;
  
lc_scac_code            
VARCHAR2(4);
  
ln_location_id           NUMBER;
  
ln_party_site_id         NUMBER;
  
lc_currency_code        
VARCHAR2(15);
  
ln_organization_id       NUMBER;
  
ln_org_id                NUMBER;
  
ln_assembly_item_id      NUMBER;
  
ln_routing_sequence_id   NUMBER;
  
lc_subinventory         
VARCHAR2(60);
  
lc_class_code           
VARCHAR2(60);
  
lc_supplier_name        
VARCHAR2(60);
  
ln_vendor_id               
NUMBER;
  
lc_supplier_site_name   
VARCHAR2(60);
  
lc_job_name               
VARCHAR2(60);
  
–================================================
  
Cursor to get the record count tobe processed
  
–================================================
  
CURSOR lcu_job_data(cp_process_flag varchar2)
   IS
     
SELECT COUNT (*)
       
FROM xxeur_discrete_job_stg
       WHERE NVL (status_flag, ‘E’) =
cp_process_flag;
  
–==========================================
  
Cursor to get the data tobe lc_cust_status
  
–==========================================
  
CURSOR lcu_main
   IS
  
SELECT *
    
FROM xxeur_discrete_job_stg
   
WHERE NVL (status_flag, ‘N’) = ‘N’;
  
–==========================================
 
Cursor to validate the Assembly Items
  
–==========================================
  
CURSOR lcu_assembly_item(cp_assembly_item VARCHAR2,cp_organization_id
     
NUMBER)
   IS
   
SELECT assembly_item_id
   
FROM  bom_bill_of_materials BBOM
        
,mtl_system_items_b MSI
   
WHERE BBOM.assembly_item_id = MSI.inventory_item_id
   
AND   BBOM.organization_id  = MSI.organization_id
   
AND   MSI.segment1          = cp_assembly_item
   
AND   MSI.organization_id   =cp_organization_id;
    
–==========================================
——Cursor to Validate routings.
   
–==========================================
  
CURSOR lcu_routing(cp_assembly_item_id VARCHAR2,cp_organization_id
NUMBER)
   IS
   
SELECT routing_sequence_id
   
FROM bom_operational_routings  BOR
   
WHERE BOR.assembly_item_id = cp_assembly_item_id
   
AND   BOR.organization_id  = cp_organization_id;
    
–==========================================
—-Cursor to Validate Inventory Org
   
–==========================================
  
CURSOR lcu_org(cp_organization_code VARCHAR2)
   IS
  
SELECT OOD.organization_id, OOD.operating_unit
    
FROM org_organization_definitions OOD
   
WHERE UPPER(organization_code) = UPPER(TRIM(cp_organization_code));
    
–==========================================
———–Cursor to validate Sub Inventory
   
–==========================================
  
CURSOR lcu_subinv(cp_subinv VARCHAR2,cp_organization_id NUMBER)
   IS
  
SELECT secondary_inventory_name
  
FROM mtl_secondary_inventories
  
WHERE secondary_inventory_name=cp_subinv
  
AND 
organization_id=cp_organization_id ;
 –==========================================
 —-Cursor to validate class code
 –==========================================
 CURSOR lcu_class_code(cp_organization_id
NUMBER)
   IS
  
SELECT class_code
  
FROM wip_accounting_classes
  
WHERE organization_id=cp_organization_id;
 
–==========================================
 
Cursor to validate the Supplier Name
  
–==========================================
  
CURSOR lcu_supplier_name(cp_vendor_name VARCHAR2)
   IS
   
SELECT vendor_name,vendor_id
   
FROM  ap_suppliers
    WHERE
vendor_name = cp_vendor_name
   
AND enabled_flag = ‘Y’;
  
–==========================================
 
Cursor to validate the Supplier Site Code
  
–==========================================
  
CURSOR lcu_supplier_site_name(cp_supplier_site_name VARCHAR2,cp_org_id
NUMBER,cp_vendor_id NUMBER)
   IS
   
SELECT vendor_site_id
   
FROM  ap_supplier_sites_all
   
WHERE vendor_site_code = cp_supplier_site_name
   
AND org_id =  cp_org_id
   
AND vendor_id = cp_vendor_id
   
AND NVL(inactive_date,TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
    
–==========================================
 
Cursor to validate the JOB NAME
  
–==========================================
  
CURSOR lcu_job_name(cp_job_name VARCHAR2)
   IS
   
SELECT JOB_NAME
   
FROM   APPS.WIP_JOB_SCHEDULE_INTERFACE_V
   
WHERE  JOB_NAME = cp_job_name;
BEGIN
  
ln_count :=0;
  
OPEN lcu_job_data(‘N’);
  
FETCH lcu_job_data
  
INTO ln_count;
  
CLOSE lcu_job_data;
  
fnd_file.put_line (fnd_file.LOG, RPAD (‘ ‘, 80, ‘ ‘));
  
fnd_file.put_line (fnd_file.LOG, ‘   
—– VALIDATE_CUSTOMERS —–‘);
  
fnd_file.put_line (fnd_file.LOG, RPAD (‘ ‘, 80, ‘ ‘));
  
fnd_file.put_line (fnd_file.LOG, RPAD (‘ ‘, 80, ‘ ‘));
  
fnd_file.put_line (fnd_file.LOG, ‘   
—– VALIDATE_CUSTOMERS —–‘);
  
fnd_file.put_line (fnd_file.LOG, RPAD (‘ ‘, 80, ‘ ‘));
  
fnd_file.put_line(fnd_file.LOG,
     
    Number of Carriers To be
Validated      :-> ‘ || ln_count);
  
BEGIN
    
— fnd_global.apps_initialize (3072, 20634, 401);
     
FOR lcv_main IN lcu_main
     
LOOP
        
–==================================
        
— Initializing the cursor variables
        
–==================================
        
lcv_main.error_message := ‘ERRM-VL> ‘;
        
lcv_main.status_flag   := ‘V’;
     
   ln_org_id                := NULL;
        
ln_vendor_id            := NULL;
        
–DBMS_OUTPUT.put_line(‘Start Validation-1’);
        
fnd_file.put_line(fnd_file.LOG,’Start Validation-1′);
        
–VAlidation inventory org —
        
IF TRIM(lcv_main.organization_code) IS NULL THEN
           
lcv_main.error_message := lcv_main.error_message||
     
‘ Organization Code is Required, ‘;
           
lcv_main.status_flag  := ‘E’;
        
ELSE
           
fnd_file.put_line (fnd_file.LOG, ‘TRACK1’);
           
ln_organization_id :=NULL;
           
OPEN lcu_org (TRIM(lcv_main.organization_code));
           
FETCH lcu_org
           
INTO ln_organization_id,ln_org_id;
           
CLOSE lcu_org;
           
IF ln_organization_id IS NOT NULL THEN
            
fnd_file.put_line (fnd_file.LOG, ‘ln_organization_id’||
     
ln_organization_id);
            
lcv_main.organization_id      :=
ln_organization_id;
           
ELSE
            
lcv_main.error_message := lcv_main.error_message||
     
‘ Organization Code is Invalid, ‘;
            
lcv_main.status_flag  := ‘E’;
           
END IF;
        
END IF;
        
IF TRIM(lcv_main.assembly_item) IS NULL THEN
           
lcv_main.error_message := lcv_main.error_message||
     
‘ Assembly  Item is Required, ‘;
           
lcv_main.status_flag  := ‘E’;
        
ELSE
           
ln_assembly_item_id := NULL;
           
OPEN lcu_assembly_item(lcv_main.assembly_item,lcv_main.
     
organization_id);
           
FETCH lcu_assembly_item
            
INTO ln_assembly_item_id;
           
CLOSE lcu_assembly_item;
           
IF ln_assembly_item_id IS NULL THEN
               lcv_main.error_message :=
lcv_main.error_message||’ ‘||lcv_main
     
.assembly_item||’ is Invalid, ‘;
               lcv_main.status_flag  := ‘E’;
           
ELSE
              
lcv_main.assembly_item_id:=ln_assembly_item_id;
           
END IF;
        
END IF;
        
IF lcv_main.job_name IS NULL THEN
           
lcv_main.job_name 
:=WIP_JOB_NUMBER_S.NEXTVAL;
     
   END IF;
        
fnd_file.put_line (fnd_file.LOG,lcv_main.status_flag);
        
fnd_file.put_line (fnd_file.LOG,’Start Validation-10′);
        
–DBMS_OUTPUT.put_line(‘Start Validation-10’);
        
— Validating the country for Customer —-
      
  IF lcv_main.assembly_item_id IS
NOT NULL THEN
           
ln_routing_sequence_id := NULL;
           
OPEN lcu_routing(lcv_main.assembly_item_id,lcv_main.
     
organization_id);
           
FETCH lcu_routing
            
INTO ln_routing_sequence_id;
            CLOSE lcu_routing;
           
IF ln_routing_sequence_id IS NULL THEN
               lcv_main.error_message :=
lcv_main.error_message||
     
‘ Routing is Not defined for this Assembly Item, ‘;
               lcv_main.status_flag  := ‘E’;
            
END IF;
        
END IF;
        
IF lcv_main.start_quantity  IS
NULL THEN
           
lcv_main.error_message := lcv_main.error_message||
     
‘ Start Quantity is Required, ‘;
           
lcv_main.status_flag  := ‘E’;
        
END IF;
      
  IF lcv_main.net_quantity  IS NULL THEN
           
lcv_main.error_message := lcv_main.error_message||
     
‘ Net Quantity is Required, ‘;
           
lcv_main.status_flag  := ‘E’;
        
END IF;
        
IF lcv_main.first_unit_start_date 
IS NULL THEN
           
lcv_main.error_message := lcv_main.error_message||
     
‘ First Unit Start Date is Required, ‘;
           
lcv_main.status_flag  := ‘E’;
        
END IF;
        
IF TRIM(lcv_main.completion_subinventory) IS NOT NULL THEN
           
lc_subinventory := NULL;
           
OPEN lcu_subinv(TRIM(lcv_main.completion_subinventory),lcv_main.
     
organization_id);
           
FETCH lcu_subinv
            
INTO lc_subinventory;
           
CLOSE lcu_subinv;
           
IF lc_subinventory IS NULL THEN
               lcv_main.error_message :=
lcv_main.error_message||
     
‘ Sub-Inventory is Invalid, ‘;
               lcv_main.status_flag  := ‘E’;
            
END IF;
        
END IF;
        
IF TRIM(lcv_main.class_code) IS NULL THEN
           
lcv_main.error_message := lcv_main.error_message||
     
‘ Class Code is Required, ‘;
           
lcv_main.status_flag  := ‘E’;
        
ELSE
           
lc_class_code := NULL;
           
OPEN lcu_class_code(lcv_main.organization_id);
       
    FETCH lcu_class_code
            
INTO lc_class_code;
           
CLOSE lcu_class_code;
           
IF lc_class_code IS NULL THEN
               lcv_main.error_message :=
lcv_main.error_message||’ ‘||lcv_main
     
.class_code||’ is Invalid, ‘;
      
        lcv_main.status_flag  := ‘E’;
           
END IF;
        
END IF;
        
IF lcv_main.net_quantity  IS NULL
THEN
           
lcv_main.error_message := lcv_main.error_message||
     
‘ Net Quantity is Required, ‘;
           
lcv_main.status_flag  := ‘E’;
        
END IF;
       
–status_type 1.UnReleased 3. Released 4.Complete 6.On Hold 7. Cancelled
        
IF UPPER(TRIM(lcv_main.status_type_name)) =’UNRELEASED’ THEN
           
lcv_main.status_type  := 1;
        
ELSIF UPPER(TRIM(lcv_main.status_type_name)) =’RELEASED’ THEN
           
lcv_main.status_type  := 3;
        
ELSIF UPPER(TRIM(lcv_main.status_type_name)) =’COMPLETE’ THEN
           
lcv_main.status_type  := 4;
        
ELSIF UPPER(TRIM(lcv_main.status_type_name)) LIKE ‘ON%HOLD%’ THEN
           
lcv_main.status_type  := 6;
        
ELSIF UPPER(TRIM(lcv_main.status_type_name)) = ‘CANCELLED’ THEN
           
lcv_main.status_type  := 7;
        
ELSE
           
lcv_main.error_message := lcv_main.error_message||
     
‘ Status is Invalid, ‘;
           
lcv_main.status_flag  := ‘E’;
        
END IF;
        
IF UPPER(TRIM(lcv_main.job_type)) =’STANDARD’ THEN
           
lcv_main.load_type  := 1;
        
ELSIF UPPER(TRIM(lcv_main.job_type)) LIKE ‘NON%STANDARD%’ THEN
           
lcv_main.status_type  := 4;
        
ELSE
           
lcv_main.error_message := lcv_main.error_message||
     
‘ Job Type is Invalid, ‘;
           
lcv_main.status_flag  := ‘E’;
        
END IF;
         
IF TRIM(lcv_main.supplier_name) IS NULL THEN
           
lcv_main.error_message := lcv_main.error_message||’ Supplier Name is
Required, ‘;
           
lcv_main.status_flag  := ‘E’;
        
ELSE
           
lc_supplier_name := NULL;
           
OPEN lcu_supplier_name(lcv_main.supplier_name);
           
FETCH lcu_supplier_name
            
INTO lc_supplier_name,ln_vendor_id;
           
CLOSE lcu_supplier_name;
           
IF ln_vendor_id IS NULL THEN
               lcv_main.error_message :=
lcv_main.error_message||’ ‘||’Supplier Does Not Exists, ‘;
               lcv_main.status_flag  := ‘E’;
           
END IF;
        
END IF;
        
————————————————————–
        
IF TRIM(lcv_main.supplier_site_name) IS NULL THEN
           
lcv_main.error_message := lcv_main.error_message||’ Supplier Site Name
is Required, ‘;
           
lcv_main.status_flag  := ‘E’;
        
ELSIF (TRIM(lcv_main.supplier_site_name) IS NOT NULL AND ln_vendor_id IS
NOT NULL ) THEN
           
lc_supplier_site_name := NULL;
           
OPEN
lcu_supplier_site_name(lcv_main.supplier_site_name,ln_org_id,ln_vendor_id);
           
FETCH lcu_supplier_site_name
            
INTO lc_supplier_site_name;
           
CLOSE lcu_supplier_site_name;
                IF lc_supplier_site_name IS
NULL THEN
               lcv_main.error_message :=
lcv_main.error_message||’ ‘||’Supplier Site Name Does Not Exists, ‘;
               lcv_main.status_flag  := ‘E’;
                END IF;
        
END IF;
        
———————————————————————
        
IF TRIM(lcv_main.job_name) IS NULL THEN
           
lcv_main.error_message := lcv_main.error_message||’ Job Name is
Required, ‘;
           
lcv_main.status_flag  := ‘E’;
        
ELSE
           
lc_job_name := NULL;
           
OPEN lcu_job_name(lcv_main.job_name);
           
FETCH lcu_job_name
            
INTO lc_job_name;
           
CLOSE lcu_job_name;
           
IF lc_job_name IS NOT NULL THEN
               lcv_main.error_message :=
lcv_main.error_message||’ ‘||’Job Name Already Exists, ‘;
               lcv_main.status_flag  := ‘E’;
           
END IF;
        
END IF;
        
———————————————————————
        
IF lcv_main.status_flag <> ‘E’ THEN
           
lcv_main.error_message := ‘All Validations Passed’;
           
lcv_main.status_flag  := ‘V’;
        
ELSE
           
lcv_main.status_flag  := ‘VE’;
        
END IF;
         fnd_file.put_line
(fnd_file.LOG,’lcv_main.status_flag’||lcv_main.
     
status_flag);
        
fnd_file.put_line (fnd_file.LOG,’lcv_main.error_message’||lcv_main.
     
error_message);
        
UPDATE xxeur_discrete_job_stg
           
SET last_update_date          =
gd_sysdate
               ,last_updated_by           = gn_user_id
               ,assembly_item_id          = lcv_main.assembly_item_id
               ,organization_id           = lcv_main.organization_id
               ,job_name                  = lcv_main.job_name
               ,status_type               = lcv_main.status_type
               ,load_type                 = lcv_main.load_type
               ,status_flag               = lcv_main.status_flag
               ,error_message             = lcv_main.error_message
         
WHERE record_id                 =
lcv_main.record_id;
         
fnd_file.put_line (fnd_file.LOG,’lcv_main.row_id’||lcv_main.
     
record_id);
     
END LOOP;
  
END;
  
COMMIT;
  
ln_count := 0;
  
OPEN lcu_job_data(‘V’);
   FETCH lcu_job_data
   
INTO ln_count;
  
CLOSE lcu_job_data;
  
fnd_file.put_line(fnd_file.LOG,
     
    Number of Customers
Validated            :-> ‘ ||
ln_count);
  
fnd_file.put_line (fnd_file.LOG, RPAD (‘ ‘, 80, ‘ ‘));
  
fnd_file.put_line (fnd_file.LOG, ‘   
—– VALIDATE_CUSTOMERS Exit —–‘)
     
;
EXCEPTION WHEN OTHERS THEN
  
fnd_file.put_line (fnd_file.LOG,’ Error While Loading ValidateCust – ‘||
     
SQLERRM);
  
–DBMS_OUTPUT.put_line(‘Error’||SQLERRM);
END validate_jobs;
——————————————————————————————————————–
— Loading the Staging Table Into Interface
Tables ( wip_job_schedule_interface )
———————————————————————————————————————
PROCEDURE load_data
IS
  
ln_progress        NUMBER:=0;
  
ln_count           NUMBER:=0;
  
ln_processed       NUMBER:=0;
  
x_location_id          NUMBER;
  
x_return_status       
VARCHAR2(2000);
  
x_msg_count            NUMBER;
  
x_msg_data            
VARCHAR2(2000);
  
x_party_site_id        NUMBER;
  
x_party_site_number   
VARCHAR2(2000);
  
x_cust_account_id      NUMBER;
  
x_account_number       NUMBER;
  
x_party_id             NUMBER;
  
x_party_number         NUMBER;
  
x_profile_id           NUMBER;
  
ln_party_site_id       NUMBER;
  
ln_party_id            NUMBER;
  
lc_location            NUMBER;
  
–============================================
  
To get the count of records to be processed
  
–============================================
  
CURSOR lcu_count_new(p_record_status VARCHAR2)
   IS
  
SELECT COUNT(*)
  
FROM xxapl.xxeur_discrete_job_stg
  
WHERE NVL(status_flag,’E’) =’V’;
  
–===============================
  
To get records to be processed
  
–===============================
  
CURSOR lcu_main
   IS
   
SELECT *
   
FROM xxeur_discrete_job_stg
   
WHERE NVL(status_flag,’N’) =’V’;
  
ln_location_id   number;
BEGIN
 
ln_progress :=1;
  FOR
lt_load_job IN lcu_main
 
LOOP
     
lt_load_job.error_message := ‘ERRM-PL> Loaded Into Interface Table’;
     
lt_load_job.status_flag   := ‘PL’;
    
fnd_file.put_line (fnd_file.LOG,’Loading into wip_job_schedule_interface
table’||lt_load_job.record_id);
    
BEGIN
      
INSERT INTO wip_job_schedule_interface
           
(
              organization_id
           
, primary_item_id
     
      , job_name
           
, start_quantity
           
, net_quantity
           
, first_unit_start_date
           
, class_code
           
, status_type
           
, completion_subinventory
           
, GROUP_ID
           
, load_type
           
, process_phase
           
, process_status
           
, created_by
           
, creation_date
           
, last_updated_by
           
, last_update_date
           
)
           
VALUES
           
(
            
lt_load_job.organization_id
           
,lt_load_job.assembly_item_id
           
,lt_load_job.job_name
           
,lt_load_job.start_quantity
           
,lt_load_job.net_quantity
           
,lt_load_job.first_unit_start_date
           
,lt_load_job.class_code
           
,lt_load_job.status_type
     
–status_type 1.UnReleased 3. Released 4.Complete 6.On Hold 7. Cancelled
           
,lt_load_job.completion_subinventory
           
,9 — group_id
           
,lt_load_job.load_type — load_type
           
,2           
           
,1
           
,fnd_global.user_id — created_by
           
, SYSDATE — creation_date
           
,fnd_global.user_id — last_updated_by
           
, SYSDATE — last_update_date
           
);
    
EXCEPTION
        
WHEN OTHERS THEN
          
lt_load_job.error_message :=SQLERRM;
          
lt_load_job.status_flag   := ‘PE’;
    
END;
   
fnd_file.put_line (fnd_file.LOG,’Updating xxeur_discrete_job_stg
Table’);
   
UPDATE xxeur_discrete_job_stg
      
SET status_flag               =
lt_load_job.status_flag
         
,error_message             =
lt_load_job.error_message
    
WHERE record_id                 =
lt_load_job.record_id;
  END
LOOP;
 COMMIT;
EXCEPTION
 WHEN
OTHERS THEN
  
apps.fnd_file.put_line (apps.fnd_file.LOG,’CRT-JOB-ERR
@’||ln_progress||’,’
     
|| SQLERRM);
  
apps.fnd_file.put_line(apps.fnd_file.output,RPAD(‘ ‘, 80, ‘ ‘));
  
apps.fnd_file.put_line (apps.fnd_file.LOG,’    —– Load Exit —–‘);
  
apps.fnd_file.put_line (apps.fnd_file.LOG,RPAD(‘ ‘, 80, ‘ ‘));
END load_data;
——————————————————————————————————————–
— Creating the JOBS using API.
———————————————————————————————————————
PROCEDURE process_data
IS
  
ln_progress            
NUMBER:=-1;
  
ln_count                NUMBER:=0;
  
ln_return_id           
NUMBER:=NULL;
  
lc_return_status       
VARCHAR2(30);
  
ln_msg_count            NUMBER;
  
lc_msg_data            
VARCHAR2(4000);
  
ln_trans_count          NUMBER;
  
ln_rec_id               NUMBER;
——————————————————————————————————————–
— Cursor picks the record id’s for all the
rows which are in interface table from Staging table
———————————————————————————————————————
  
CURSOR lcu_process_data
   
IS
   
SELECT win.job_name job_name,stg.record_id record_id, win.process_status
process_status FROM
     
xxeur_discrete_job_stg stg, wip_job_schedule_interface win
WHERE win.job_name = stg.job_name
AND win.GROUP_ID = 9
AND win.process_status = 4
AND stg.status_flag = ‘PL’;
——————————————————————————————————————–
— Cursor picks all the records which are
successfully created.
———————————————————————————————————————
  
CURSOR lcu_success_data
   
IS
   
SELECT win.job_name job_name,stg.record_id record_id, win.process_status
process_status FROM
     
xxeur_discrete_job_stg stg, wip_job_schedule_interface win
WHERE win.job_name = stg.job_name
AND win.GROUP_ID = 9
AND win.process_status = 4;
–AND stg.status_flag = ‘PL’;
——————————————————————————————————————–
— Cursor picks all the records which are
Errored.
———————————————————————————————————————
   
CURSOR lcu_error_data
   
IS
   
SELECT win.job_name job_name,stg.record_id record_id, win.process_status
process_status,wie.error error
 FROM     
xxeur_discrete_job_stg stg, wip_job_schedule_interface
win,wip_interface_errors wie
WHERE win.job_name = stg.job_name
AND win.interface_id = wie.interface_id
AND win.GROUP_ID = 9
AND win.process_status = 3;
BEGIN
  
apps.fnd_file.put_line(apps.fnd_file.LOG,
     
   ——— Procedure
PROCESS_DATA_API ——–‘);
  
apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(‘ ‘,80,’ ‘));
  
apps.fnd_file.put_line(apps.fnd_file.output,
     
   ——— Procedure
PROCESS_DATA_API ——–‘);
  
apps.fnd_file.put_line(apps.fnd_file.output,RPAD(‘ ‘,80,’ ‘));
     
BEGIN
        
wip_massload_pub.massloadjobs(p_groupid       => 9,
                                     
p_validationlevel => 2,
                                     
p_commitflag      => 1,
                                     
x_returnstatus    =>
lc_return_status,
                                     
x_errormsg        =>
lc_msg_data);
     
EXCEPTION
     
WHEN OTHERS THEN
        
apps.fnd_file.put_line(apps.fnd_file.LOG,’PROCESS-DATA-API>’||SQLERRM
     
||’, ‘||SQLCODE);
     
END;
      
FOR l_rec IN lcu_success_data
      
LOOP
      
fnd_file.put_line (fnd_file.LOG,’Updating xxeur_discrete_job_stg
Table’||l_rec.record_id);
      
UPDATE xxeur_discrete_job_stg
       
SET status_flag               =
l_rec.process_status
         
,error_message             =
‘Complete’
       
WHERE record_id                 =
l_rec.record_id;
      
END LOOP;
       
COMMIT;
       
FOR l_rec IN lcu_error_data
      
LOOP
      
fnd_file.put_line (fnd_file.LOG,’Updating xxeur_discrete_job_stg
Table’||l_rec.record_id);
      
UPDATE xxeur_discrete_job_stg
       
SET status_flag               =
l_rec.process_status
         
,error_message             =
l_rec.error
       
WHERE record_id                 =
l_rec.record_id;
      
END LOOP;
       
COMMIT;
  
apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(‘ ‘,80,’ ‘));
  
apps.fnd_file.put_line(apps.fnd_file.LOG,
     
   ——— Procedure
PROCESS_DATA_API Exit ——–‘);
  
apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(‘ ‘,80,’ ‘));
  
apps.fnd_file.put_line(apps.fnd_file.LOG,
     
   ——— Procedure
PROCESS_DATA_API Exit ——–‘);
  
–po_auto_create;
EXCEPTION
WHEN OTHERS THEN
  
apps.fnd_file.put_line(apps.fnd_file.LOG,’PROCESS-DATA-API-END>’||SQLERRM
||
     
‘, ‘||SQLCODE);
  
apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(‘ ‘,80,’ ‘));
  
apps.fnd_file.put_line(apps.fnd_file.LOG,
     
   ——— Procedure
PROCESS_DATA_API Exit ——–‘);
  
apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(‘ ‘,80,’ ‘));
END process_data;
——————————————————————————————————————–
— Procedure to create the Purchased Order
using API.
———————————————————————————————————————
PROCEDURE po_auto_create
IS
CURSOR lcu_stg_data IS
SELECT * FROM xxeur_discrete_job_stg
WHERE status_flag = ‘4’;
   
ln_interface_header_id NUMBER;
   
ln_interface_line_id   NUMBER;
   
ln_po_req_line_id      NUMBER:=0;
   
ln_po_req_line_num     NUMBER:=0;
   
lc_process_code       
VARCHAR2(20) := ‘NEW’;
     
— NEW in case of creation of new PO, ADD incase of adding line to PO
   
lc_action             
VARCHAR2(20) := ‘NEW’;
     
— NEW in case of creation of new PO, ADD incase of adding line to PO
   
ln_document_id         NUMBER :=
NULL;
     
— assign PO number in case of adding lines to existing PO…
   
ln_document_num       
VARCHAR2(20) := NULL;
     
— assign PO number in case of adding lines to existing PO…
   
ln_agent_id            NUMBER:=0 ;
   
ln_vendor_id           NUMBER;
   
ln_vendor_site_id      NUMBER;
   
lc_return_status      
VARCHAR2(1);
   
ln_msg_count           NUMBER;
   
lc_msg_data           
VARCHAR2(5000);
   
ln_num_lines_processed NUMBER;
   
lc_approval_status    
VARCHAR2(25):=’APPROVED’;
   
lc_document_number    
po_headers_all.segment1%TYPE;
BEGIN
   
mo_global.init(‘PO’);
   
mo_global.set_policy_context(‘S’,1256);
   
FOR l_rec IN lcu_stg_data
   
LOOP
   
SELECT po_headers_interface_s.NEXTVAL
   
INTO ln_interface_header_id
   
FROM DUAL;
   
apps.fnd_file.put_line(apps.fnd_file.LOG,’ln_interface_header_id=’||ln_interface_header_id);
   
apps.fnd_file.put_line(apps.fnd_file.LOG,’ vendor_name’||l_rec.supplier_name);
   
apps.fnd_file.put_line(apps.fnd_file.LOG,’
vendor_site_code’||l_rec.supplier_site_name);
   
ln_vendor_id       := NULL;
   
ln_vendor_site_id  := NULL;
   
ln_agent_id        := NULL;
   
ln_po_req_line_id  := NULL;
   
ln_po_req_line_num := NULL;
       
BEGIN
       
SELECT PV.vendor_id,vendor_site_id
         
INTO ln_vendor_id,ln_vendor_site_id
         
FROM po_vendors PV
              ,po_vendor_sites_all PVS
        
WHERE vendor_name          =
l_rec.supplier_name–‘AMBIOS’
          
AND PV.vendor_id         =
PVS.vendor_id
          
AND PVS.vendor_site_code = l_rec.supplier_site_name;–‘FARMEA’;
         
apps.fnd_file.put_line(apps.fnd_file.LOG,’
vendor_name’||l_rec.supplier_name);
         
apps.fnd_file.put_line(apps.fnd_file.LOG,’
vendor_site_code’||l_rec.supplier_site_name);
       
EXCEPTION WHEN OTHERS THEN
           
apps.fnd_file.put_line(apps.fnd_file.LOG,’Exception in Vendor Name
Query’);
           
apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(‘ ‘,80,’ ‘));
           
apps.fnd_file.put_line(apps.fnd_file.LOG,SQLERRM ||’, ‘||SQLCODE);
       
END;
     
apps.fnd_file.put_line(apps.fnd_file.LOG,’Level 1′);
     
apps.fnd_file.put_line(apps.fnd_file.LOG,’ job_name’||l_rec.job_name);
       
BEGIN
        SELECT prl.to_person_id,
prl.requisition_line_id, prl.line_num
         
INTO ln_agent_id, ln_po_req_line_id, ln_po_req_line_num
         
FROM po_requisition_lines_all prl, wip_job_schedule_interface win
        
WHERE prl.wip_entity_id = win.wip_entity_id
          
AND win.job_name = l_rec.job_name;
           
apps.fnd_file.put_line(apps.fnd_file.LOG,’ job_name’||l_rec.job_name);
           
apps.fnd_file.put_line(apps.fnd_file.LOG,’
vendor_site_code’||l_rec.supplier_site_name);
           
apps.fnd_file.put_line(apps.fnd_file.LOG,’ Agent Id’||ln_agent_id);
           
apps.fnd_file.put_line(apps.fnd_file.LOG,’ PO Req Line
Id’||ln_po_req_line_id);
       
EXCEPTION WHEN OTHERS THEN
           
apps.fnd_file.put_line(apps.fnd_file.LOG,’Exception in Agent Id Query’);
           
apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(‘ ‘,80,’ ‘));
           
apps.fnd_file.put_line(apps.fnd_file.LOG,SQLERRM ||’, ‘||SQLCODE);
       
END;
     
apps.fnd_file.put_line(apps.fnd_file.LOG,’ln_agent_id=’||ln_agent_id);
   
/* code to get agent_id */
   
apps.fnd_file.put_line(apps.fnd_file.LOG,’Inserting into Header PO
interface’);
   
INSERT INTO po_headers_interface
                ( interface_header_id
                ,interface_source_code     
                ,batch_id
                ,process_code
                ,action
                ,document_type_code
                ,document_subtype
                ,document_num
                — ,freight_terms
                — ,FOB
                –,payment_terms
                ,group_code
                ,vendor_id
                ,vendor_site_id
                ,agent_id
                ,currency_code
                ,creation_date
                ,created_by
                ,last_update_date
                ,last_updated_by
                ,style_id
                ,approval_status
                ,approved_date
                )
         
VALUES
               ( ln_interface_header_id
                ,’CONSUMPTION_ADVICE’                                   
                ,ln_interface_header_id —
x_batch_id
                ,lc_process_code — process
code
                ,lc_action — action
                ,’PO’ — x_document_type
                ,’STANDARD’ —
document_subtype
        
       ,l_rec.PURCHASE_ORDER_NUMBER–ln_document_num–
— document_num
                — ,p_freight_terms
                — ,p_FOB
                –,payment_terms
                ,’DEFAULT’ — ‘DEFAULT’ /
‘REQUISITION’
                ,ln_vendor_id — x_vendor_id
                ,ln_vendor_site_id —
x_vendor_site_id
                ,ln_agent_id — x_agent_id
                ,’EUR’ — x_currency_code
                ,SYSDATE — x_creation_date
                ,-1 — x_created_by
                ,SYSDATE — x_last_update_date
                ,-1 — x_last_updated_by
                ,1 — style_id
                ,lc_approval_status
                ,SYSDATE
                );
   
apps.fnd_file.put_line(apps.fnd_file.LOG,’Inserting into LIne PO
interface’);
   
SELECT po_lines_interface_s.NEXTVAL
   
INTO ln_interface_line_id
   
FROM DUAL;
   
INSERT INTO po_lines_interface
           
( interface_header_id
           
,interface_line_id
           
,requisition_line_id
           
,line_num
           
,unit_price
           
,creation_date
           
,created_by
           
,last_update_date
           
,last_updated_by
           
)
   
VALUES
          
( ln_interface_header_id — x_interface_header_id,
           
,ln_interface_line_id — x_interface_line_id,
           
,ln_po_req_line_id — x_requisition_line_id, from req lines
           
,ln_po_req_line_num — x_so_line_num, from SO lines
           
,l_rec.price
            ,SYSDATE — x_creation_date
           
,-1 — x_created_by
           
,SYSDATE — x_last_update_date
           
,-1 — x_last_updated_by
           
);
           
COMMIT;
               
apps.fnd_file.put_line(apps.fnd_file.LOG,’Calling…
PO_INTERFACE_S.create_documents’);
          
PO_INTERFACE_S.create_documents (
                                            
p_api_version              =>
1.0
                                            ,x_return_status            => lc_return_status
                                           
,x_msg_count                =>
ln_msg_count
                                           
,x_msg_data                 =>
lc_msg_data
                                            ,p_batch_id                 => ln_interface_header_id
                                           
,p_req_operating_unit_id    =>
1256
                                            ,p_purch_operating_unit_id  => 1256
                                           
,x_document_id              =>
ln_document_id
                                           
,x_number_lines             => ln_num_lines_processed
                                           
,x_document_number          =>
ln_document_num
                                           
,p_document_creation_method => NULL
                                           
,p_sourcing_k_doc_type      =>
NULL
                                           
,p_conterms_exist_flag      =>
‘N’
                                           
,p_orig_org_id              =>
NULL
                                            );
       
IF lc_return_status = ‘S’ THEN
        
apps.fnd_file.put_line(apps.fnd_file.LOG,’API Success’);
           
UPDATE xxeur_discrete_job_stg
           
SET status_flag               =
‘S’
              ,error_message             = ‘Successfully Create PO.’ ||
ln_document_num
              ,PO_NUM                      = ln_document_num
           
WHERE record_id                 =
l_rec.record_id;
           
–Updating the consigned_consumption_flag = NULL in the Base Table
           
–in order to display the PO in Purchased Order Form if it successfully
created.
            
UPDATE po_headers_all
                SET consigned_consumption_flag
= NULL
                WHERE po_header_id =
ln_document_id;
       
ELSE
       
apps.fnd_file.put_line(apps.fnd_file.LOG,’API Error’);
       
UPDATE xxeur_discrete_job_stg
           
SET status_flag               =
‘E’
     
        ,error_message             = ‘Error while Creating PO.’ ||
SUBSTR(
         
lc_msg_data,1,25)
           
WHERE record_id                 =
l_rec.record_id;
       
END IF;
   
END LOOP;
   
COMMIT;
   
apps.fnd_file.put_line(apps.fnd_file.LOG,’l_return_status: ‘ ||
lc_return_status);
   
apps.fnd_file.put_line(apps.fnd_file.LOG,’l_msg_count: ‘ ||
ln_msg_count);
   
apps.fnd_file.put_line(apps.fnd_file.LOG,’l_msg_data:’ ||
SUBSTR(lc_msg_data,1,240));
   
apps.fnd_file.put_line(apps.fnd_file.LOG,’l_document_id: ‘ ||
ln_document_id);
   
apps.fnd_file.put_line(apps.fnd_file.LOG,’l_document_number: ‘ ||
ln_document_num);
EXCEPTION WHEN OTHERS THEN
  
apps.fnd_file.put_line(apps.fnd_file.LOG,’PROCESS-DATA-END>’||SQLERRM
||’, ‘||SQLCODE);
  
apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(‘ ‘,80,’ ‘));
  
apps.fnd_file.put_line(apps.fnd_file.LOG,’   ——— Procedure PO AUTO CREATE Exception
——–‘);
  
apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(‘ ‘,80,’ ‘));
   
apps.fnd_file.put_line(apps.fnd_file.LOG,SQLERRM ||’, ‘||SQLCODE);
END po_auto_create;
END XXEUR_CREATE_DISCRETE_JOB;
/

 

  • June 9, 2016 | 25 views