How to create ap invoice by using interface tables

DECLARE

CURSOR c_hdr
IS
SELECT *
    FROM APINV_HDR_STG
    WHERE 1=1
and STATUS ='STAGED'
;

CURSOR c_line ( ln_inv_id NUMBER )
IS
   SELECT *
    FROM APINV_LINES_STG
    WHERE invoice_id = ln_inv_id;
 

CURSOR get_org_id IS
SELECT DISTINCT org_id
    FROM apinv_hdr_stg hdr
    WHERE 1 = 1
    AND hdr.status = 'INTERFACED';

 
l_errbuf VARCHAR2(2000);
l_retcode VARCHAR2(2000);  
lc_final_flag VARCHAR2 (1) := 'Y';

lc_header_flag VARCHAR2 (1) := 'Y';
lc_line_flag    VARCHAR2 (1) := 'Y';
ln_count        NUMBER        := 0;
l_conc_request_id NUMBER := 0;
l_batch_name      VARCHAR2(100);
l_phase            VARCHAR2(25);
l_status          VARCHAR2(25);
l_dev_phase        VARCHAR2(25);
l_dev_status      VARCHAR2(25);
l_message          VARCHAR2(500);
l_request_status  BOOLEAN;
l_error_message VARCHAR2(2000);
l_int_status VARCHAR2(100);
l_int_error VARCHAR2(4000);
l_invoice_id NUMBER;
l_invoice_line_id NUMBER;
l_line_number NUMBER := 0;
--l_int_cnt number;

BEGIN
    fnd_file.put_line (fnd_file.log ,'LOAD_INT_DATA start: ');

    FOR i IN c_hdr
    LOOP

fnd_file.put_line (fnd_file.log ,'c_hdr Loop Start');

lc_header_flag :='Y';
ln_count := ln_count + 1;
l_invoice_id    := ap_invoices_interface_s.NEXTVAL;

fnd_file.put_line (fnd_file.log ,'Interface Invoice Number: ' || i.invoice_num);

BEGIN
INSERT INTO ap_invoices_interface
(invoice_id
, invoice_num
, invoice_type_lookup_code
, invoice_date
, vendor_id
, vendor_num
, vendor_name
, vendor_site_id
, vendor_site_code
, invoice_amount
, invoice_currency_code
, description
, last_update_date
, last_updated_by
, creation_date
, created_by
, SOURCE
, request_id
, gl_date
, org_id
, pay_group_lookup_code
, payment_method_lookup_code
, external_bank_account_id
, reference_key2
, requester_id
, terms_date
, attribute2
)
VALUES (
  l_invoice_id
, i.invoice_num
, i.invoice_type_lookup_code
, i.invoice_date
, i.vendor_id
, i.vendor_num
, i.vendor_name
, i.vendor_site_id
, i.vendor_site_code
, i.invoice_amount
, i.invoice_currency_code
, i.description
, i.last_update_date
, i.last_updated_by
, i.creation_date
, i.created_by
, i.SOURCE
, i.request_id
, i.gl_date
, i.org_id
, i.pay_group_lookup_code
, i.payment_method_lookup_code
, i.external_bank_account_id
, i.report_id
, i.requester_id
, i.invoice_date
, 'No'
);

EXCEPTION  WHEN OTHERS THEN
--lc_sucss_flag := 'N';
lc_header_flag := 'N';
lc_final_flag  := 'N';
l_errbuf := SQLERRM;
l_error_message := 'Error loading header data into AP Interface : ' || l_errbuf;
fnd_file.put_line (fnd_file.log ,l_error_message);
ROLLBACK;
END;

-- Insert Lines only if Header inserted successfully
IF lc_header_flag = 'Y' THEN

lc_line_flag := 'Y';
l_line_number := 0;

FOR j IN c_line (i.invoice_id)
LOOP
l_invoice_line_id := ap_invoice_lines_interface_s.NEXTVAL;
l_line_number := l_line_number + 1;

fnd_file.put_line (fnd_file.log ,'Inside AP_interface_line for Invoice Number: ' || i.invoice_num);

BEGIN
INSERT INTO ap_invoice_lines_interface
(invoice_id
, invoice_line_id
, line_number
, line_type_lookup_code
, description
, amount
, accounting_date
, dist_code_concatenated
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, quantity_invoiced
, last_updated_by
, last_update_date
, created_by
, creation_date
, org_id
, attribute15 
, dist_code_combination_id
)
VALUES (
  l_invoice_id
, l_invoice_line_id
, l_line_number
, j.line_type_lookup_code
, j.description
, j.amount
, j.accounting_date
, j.dist_code_concatenated
, j.project_id
, j.task_id
, j.expenditure_type
, j.expenditure_item_date
, j.expenditure_organization_id
, j.quantity_invoiced
, j.last_updated_by
, j.last_update_date
, j.created_by
, j.creation_date
, j.org_id
, j.attribute1
, j.dist_code_combination_id
);

--lc_sucss_flag := 'Y';
--lc_final_flag := 'Y';
EXCEPTION WHEN OTHERS THEN
--lc_sucss_flag := 'N';
lc_line_flag := 'N';
lc_final_flag := 'N';
l_errbuf := SQLERRM;
l_error_message := 'Error While loading Line data into AP Interface : '|| l_errbuf;
fnd_file.put_line (fnd_file.log ,l_error_message);
ROLLBACK;
END;
END LOOP;

IF lc_line_flag = 'Y' THEN
UPDATE apinv_hdr_stg
SET status      = 'INTERFACED'
WHERE invoice_id = i.invoice_id;
COMMIT;
END IF;

END IF;

    END LOOP;

    IF ln_count = 0 THEN
      fnd_file.put_line (fnd_file.log ,'No records to be loaded into AP Invoice Interface Tables');
    END IF;

IF ln_count > 0 THEN

FOR get_org_id_rec IN get_org_id
LOOP

mo_global.set_policy_context('S', get_org_id_rec.org_id);
fnd_global.apps_initialize(fnd_global.user_id, fnd_global.resp_id, fnd_global.resp_appl_id);

l_batch_name := 'TEST'||to_char(sysdate,'DD-MON-RRRR HH24:MI:SS');

fnd_file.put_line (fnd_file.log ,'get_org_id_rec.org_id: '||get_org_id_rec.org_id);
fnd_file.put_line (fnd_file.log ,'l_batch_name: '||l_batch_name);


l_phase             := NULL;
l_status            := NULL;
l_dev_phase         := NULL;
l_dev_status        := NULL;
l_message     := NULL;
l_conc_request_id   := NULL;



l_conc_request_id := fnd_request.submit_request ( application => 'SQLAP'
   , program   => 'APXIIMPT'
   , argument1 => get_org_id_rec.org_id
   , argument2 => 'MANUAL INVOICE ENTRY'
   , argument3 => NULL
   , argument4 => l_batch_name
   );
COMMIT;

fnd_file.put_line (fnd_file.log ,'l_conc_request_id : ' || l_conc_request_id);

l_request_status := fnd_concurrent.wait_for_request ( l_conc_request_id
, 10
, 0
, l_phase
, l_status
, l_dev_phase
, l_dev_status
, l_message
);

fnd_file.put_line (fnd_file.log ,'l_conc_request_id1 : ' || l_conc_request_id);

END LOOP;
END IF;
fnd_file.put_line (fnd_file.log ,'LOAD_INT_DATA end: ');
EXCEPTION WHEN OTHERS THEN
    l_errbuf := SQLERRM; 
    l_retcode := 1;
fnd_file.put_line (fnd_file.log ,'Unhandled Exception While loading data into AP Interface : ' || l_errbuf);
END load_stg_data_test;
/

Query to findout rejected message and records from Payables Open Interface Import program

SELECT aii.invoice_num,aii.invoice_id,           
listagg(decode(flv.meaning, null, null,flv.meaning ||' - '|| flv.description)  ,', ') within group(order by aii.invoice_id) error_message
FROM   apps.ap_invoices_interface aii
, apps.ap_interface_rejections apir
, fnd_lookup_values flv     
WHERE 1 = 1
AND aii.invoice_id = apir.parent_id
AND apir.reject_lookup_code = flv.lookup_code
AND apir.parent_table = 'AP_INVOICES_INTERFACE'
and aii.status ='REJECTED'
AND aii.invoice_id =4080532
group by aii.invoice_num,aii.invoice_id


SELECT aii.invoice_num,aii.invoice_id,
listagg(decode(flv.meaning, null, null,flv.meaning ||' - '|| flv.description) ,', ') within group(order by aii.invoice_id) error_message
FROM   apps.ap_invoices_interface aii
 , apps.ap_invoice_lines_interface aili
 , apps.ap_interface_rejections apir
 , fnd_lookup_values flv       
WHERE 1 = 1
and aii.invoice_id = aili.invoice_id
AND aili.invoice_line_id = apir.parent_id
AND apir.reject_lookup_code = flv.lookup_code
AND apir.parent_table = 'AP_INVOICE_LINES_INTERFACE'
AND aii.status ='REJECTED'
AND aii.invoice_id =4083297
group by aii.invoice_num,aii.invoice_id

API to find out Oracle GL Account Code Combination ID for given Segments (fnd_flex_ext.get_combination_id)

PROCEDURE get_costing_segments (
      p_segment1           IN   VARCHAR2
    , p_segment2           IN   VARCHAR2
    , p_segment3           IN   VARCHAR2
    , p_segment4           IN   VARCHAR2
    , p_segment5           IN   VARCHAR2
    , p_def_code_comb_id   OUT   NUMBER
, p_message    OUT   VARCHAR2
   )
AS
l_application_short_name      VARCHAR2 (240);
    l_key_flex_code               VARCHAR2 (240);
    l_structure_num               NUMBER;
    l_validation_date             DATE;
    n_segments                    NUMBER;
    segments                      apps.fnd_flex_ext.segmentarray;
    l_combination_id              NUMBER;
    l_data_set                    NUMBER;
    l_return                      BOOLEAN;
    l_message                     VARCHAR2 (1000);

BEGIN
l_application_short_name := 'SQLGL';
l_key_flex_code := 'GL#';

SELECT id_flex_num
    INTO l_structure_num
    FROM apps.fnd_id_flex_structures
WHERE id_flex_code = 'GL#'
    AND id_flex_structure_code = 'SPWX_CHART_OF_ACCOUNTS';

l_validation_date := SYSDATE;
n_segments := 6;
segments (1) := p_segment1;
segments (2) := p_segment2;
segments (3) := p_segment3;
segments (4) := p_segment4;
segments (5) := p_segment5;
segments (6) := '000';
l_data_set := NULL;

l_return :=
     fnd_flex_ext.get_combination_id
                      (application_short_name      => l_application_short_name
                     , key_flex_code               => l_key_flex_code
                     , structure_number            => l_structure_num
                     , validation_date             => l_validation_date
                     , n_segments                  => n_segments
                     , segments                    => segments
                     , combination_id              => l_combination_id
                     , data_set                    => l_data_set
                      );

l_message := fnd_flex_ext.GET_MESSAGE;

IF l_return
THEN
debug_msg ('l_Return = TRUE');
debug_msg ( 'COMBINATION_ID = ' || l_combination_id);
p_def_code_comb_id := l_combination_id;
p_message := NULL;
   
ELSE

debug_msg ( 'Error: ' || l_message);
p_def_code_comb_id := NULL;
p_message := l_message;

END IF;

EXCEPTION WHEN OTHERS THEN
debug_msg ( 'Error: ' || SQLERRM);
p_def_code_comb_id := NULL;
l_message := l_message||'-'||SQLERRM;   
END get_costing_segments;

Query to find AP invoice status in oracle apps

SELECT DISTINCT aia.invoice_id
              , aia.org_id
              , aia.invoice_num
              , aia.invoice_type_lookup_code
              , ap_invoices_pkg.get_approval_status
                                                 (aia.invoice_id
                                                , aia.invoice_amount
                                                , aia.payment_status_flag
                                                , aia.invoice_type_lookup_code
                                                 ) status
           FROM ap_invoices_all aia
              , ap_invoice_lines_all aila
          WHERE 1 = 1
            AND aia.invoice_id = aila.invoice_id
            AND aia.org_id = aila.org_id
            AND aia.org_id = :p_org_id
            AND aia.invoice_num = :p_invoice_num

API to validate AP invoice: Change the invoice status to Validated (ap_invoices_pkg.get_approval_status)

PROCEDURE apinv_validate
( errbuf   OUT VARCHAR2
, retcode  OUT VARCHAR2
, p_request_id IN NUMBER
, p_org_id IN NUMBER DEFAULT NULL
)
AS

CURSOR cur_inv IS
SELECT DISTINCT
aia.invoice_id
, aia.org_id
, aia.invoice_num
, aia.invoice_type_lookup_code
FROM ap_invoices_all aia
, ap_invoice_lines_all aila
WHERE  1 = 1
AND aia.invoice_id = aila.invoice_id
AND aia.org_id = aila.org_id
AND ap_invoices_pkg.get_approval_status (aia.invoice_id
  , aia.invoice_amount
  , aia.payment_status_flag
  , aia.invoice_type_lookup_code
   ) NOT IN ('APPROVED', 'UNPAID')
AND aia.org_id = nvl(p_org_id,aia.org_id)
AND aila.request_id = nvl(p_request_id,aila.request_id)
;

l_hold_cnt NUMBER;

BEGIN

fnd_file.put_line (fnd_file.log , 'apinv_validate start');

mo_global.set_policy_context('S', p_org_id);
fnd_global.apps_initialize(fnd_global.user_id, fnd_global.resp_id, fnd_global.resp_appl_id);


FOR rec_inv IN cur_inv
LOOP

fnd_file.put_line (fnd_file.log ,'apinv_validate invoice_num: '||rec_inv.invoice_num);

BEGIN

l_hold_cnt := NULL;

IF (AP_APPROVAL_PKG.BATCH_APPROVAL
(p_run_option => NULL,
p_sob_id => fnd_profile.VALUE('gl_set_of_bks_id'),
p_inv_start_date => NULL,
p_inv_end_date => NULL,
p_inv_batch_id => NULL,
p_vendor_id => NULL,
p_pay_group => NULL,
p_invoice_id =>  rec_inv.invoice_id,
p_entered_by => NULL,
p_debug_switch => 'N',
p_conc_request_id => fnd_profile.VALUE('CONC_REQUEST_ID'),
p_commit_size => NULL,
p_org_id => rec_inv.org_id,
p_report_holds_count => l_hold_cnt,
p_transaction_num => NULL))
THEN
fnd_file.put_line (fnd_file.log , 'Invoice Validated: '||rec_inv.invoice_num);
ELSE
fnd_file.put_line (fnd_file.log , 'Invoice Validation Failed: '||rec_inv.invoice_num);
END IF;

COMMIT;

EXCEPTION WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log , rec_inv.invoice_num ||' Invoice Validation failed with unhandled exception.Error: '||SQLERRM);
END;
END LOOP;
fnd_file.put_line (fnd_file.log , 'apinv_validate end');
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log ,' apinv_validate failed with unhandled exception.Error: '||SQLERRM);
END;