API Script to Cancel AP Invoices (Cancel AP Invoice API )

API Script to Cancel AP Invoices (Cancel AP Invoice API )

/* Formatted on 2017/07/08 18:15 (Formatter Plus v4.8.0) */

CREATE OR REPLACE PROCEDURE cancel_invoices (ip_operating_unit IN VARCHAR2)

AS

 l_resp_id NUMBER;

 l_appl_id NUMBER;

 l_user_id NUMBER := apps.fnd_global.user_id;

 l_org_id NUMBER := apps.fnd_global.org_id;

 l_message_name VARCHAR2 (1000);

 l_invoice_amount NUMBER;

 l_base_amount NUMBER;

 l_tax_amount NUMBER;

 l_temp_cancelled_amount NUMBER;

 l_cancelled_by VARCHAR2 (1000);

 l_cancelled_amount NUMBER;

 l_cancelled_date DATE;

 l_last_update_date DATE;

 l_original_prepayment_amount NUMBER;

 l_pay_curr_invoice_amount NUMBER;

 l_token VARCHAR2 (100);

 l_boolean BOOLEAN;

 err_msg VARCHAR2 (2000);

 CURSOR invoice_cur

 IS

 SELECT aia.invoice_id, aia.last_updated_by, aia.last_update_login,

 aia.gl_date, aia.invoice_num

 FROM xx_ap_invoices_conv_stg a,

 ap_invoices_all aia,

 ap_invoice_lines_all aila

 WHERE a.ls_inv_num = aia.invoice_num

 AND a.ls_org_id = aia.org_id

 AND aia.invoice_id = aila.invoice_id

 AND aia.org_id = aila.org_id

 AND aia.payment_status_flag = 'N'

 AND NVL (aila.cancelled_flag, 'N') <> 'Y';

--- AND aia.invoice_num = '65431';

BEGIN

 BEGIN

 SELECT DISTINCT fr.responsibility_id, frx.application_id

 INTO l_resp_id, l_appl_id

 FROM apps.fnd_responsibility frx,

 apps.fnd_responsibility_tl fr

 WHERE fr.responsibility_id = frx.responsibility_id

 AND UPPER (fr.responsibility_name) LIKE

 UPPER (DECODE (ip_operating_unit,

 'OU USA MA', 'Payables Manager',

 'OU USA WI', 'OU USA WI_Payables Manager',

 'OU Austria', 'OU AUSTRIA_Payables Manager',

 'OU China', 'OU CHINA_Payables Manager'

 )

 );

 DBMS_OUTPUT.put_line ('l_resp_id => ' || l_resp_id);

 DBMS_OUTPUT.put_line ('l_appl_id => ' || l_appl_id);

 EXCEPTION

 WHEN OTHERS

 THEN

 err_msg :=

 'Error Occured while Deriving responsibility id' || SQLERRM;

 apps.fnd_file.put_line

 (apps.fnd_file.output,

 'Error Occured while Deriving responsibility id'

 );

 END;

 mo_global.set_policy_context ('S', l_org_id);

 apps.fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

 FOR l_inv_rec IN invoice_cur ----(l_org_id)

 LOOP

 DBMS_OUTPUT.put_line

 ( 'Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: '

 || l_inv_rec.invoice_num

 );

 DBMS_OUTPUT.put_line

 ('**************************************************************');

 l_boolean :=

 ap_cancel_pkg.ap_cancel_single_invoice

 (p_invoice_id => l_inv_rec.invoice_id,

 p_last_updated_by => l_inv_rec.last_updated_by,

 p_last_update_login => l_inv_rec.last_update_login,

 p_accounting_date => l_inv_rec.gl_date,

 p_message_name => l_message_name,

 p_invoice_amount => l_invoice_amount,

 p_base_amount => l_base_amount,

 p_temp_cancelled_amount => l_temp_cancelled_amount,

 p_cancelled_by => l_cancelled_by,

 p_cancelled_amount => l_cancelled_amount,

 p_cancelled_date => l_cancelled_date,

 p_last_update_date => l_last_update_date,

 p_original_prepayment_amount => l_original_prepayment_amount,

 p_pay_curr_invoice_amount => l_pay_curr_invoice_amount,

 p_token => l_token,

 p_calling_sequence => NULL

 );

 DBMS_OUTPUT.put_line ('l_message_name => ' || l_message_name);

 DBMS_OUTPUT.put_line ('l_invoice_amount => ' || l_invoice_amount);

 DBMS_OUTPUT.put_line ('l_base_amount => ' || l_base_amount);

 DBMS_OUTPUT.put_line ('l_tax_amount => ' || l_tax_amount);

 DBMS_OUTPUT.put_line ( 'l_temp_cancelled_amount => '

 || l_temp_cancelled_amount

 );

 DBMS_OUTPUT.put_line ('l_cancelled_by => ' || l_cancelled_by);

 DBMS_OUTPUT.put_line ('l_cancelled_amount => ' || l_cancelled_amount);

 DBMS_OUTPUT.put_line ('l_cancelled_date => ' || l_cancelled_date);

 DBMS_OUTPUT.put_line ('P_last_update_date => ' || l_last_update_date);

 DBMS_OUTPUT.put_line ( 'P_original_prepayment_amount => '

 || l_original_prepayment_amount

 );

 DBMS_OUTPUT.put_line ( 'l_pay_curr_invoice_amount => '

 || l_pay_curr_invoice_amount

 );

 IF l_boolean

 THEN

 DBMS_OUTPUT.put_line ( 'Successfully Cancelled the Invoice => '

 || l_inv_rec.invoice_num

 );

 COMMIT;

 ELSE

 DBMS_OUTPUT.put_line ( 'Failed to Cancel the Invoice => '

 || l_inv_rec.invoice_num

 );

 ROLLBACK;

 END IF;

 END LOOP;

END cancel_invoices;  

Oracle Apps Interview Questions

 Oracle Apps Interview Questions

 

AP (Accounts Payables)

 

1)What is the Flow of AP module or Proceure to Pay ?
ANS
--Requisition
RFQ
Quotation
Po
Receipts
invoice
Payments
Transfer to Gl
------------------------------------------------
2)How to transfer transactions from AP to GL?
--Payables transfer to GL program” is used to transfer from AP to GL
------------------------------------------------
3)What are the Base tables we have in AP?
1) ap_invoices_all

2) ap_invoice_payments_all

3) ap_invoice_distibutions_All

4) ap_payment_schdules

5) ap_payment_dustributions_all

6) ap_checks_all

7) ap_accounting_events_all

8) ap_bank_accounts_all

9) ap_bank_accounts_uses_all
------------------------------------------------
4)What is Distriutions set?
------------------------------------------------
6)What is Payment terms?
--------------------------------
7)What are the types Invoicess?
A. Standard
B. Debit Memo
C. Credit Memo
D. Pre-Payment
E. Expense Report
F. Withholding Tax Invoice
G. Miscellaneous Invoice
------------------------------------
8)What is Credit memo and Debit Memo?
9)What are the types of Payments we have?
Check – You can pay with a manual payment, a Quick payment, or in a payment batch.
• Clearing – Used for recording invoice payments to internal suppliers.
• Electronic – You generate an electronic payment file that you deliver to your bank to create payments. Use Electronic if the invoice will be paid using EFT or EDI.
• Wire – Used to manually record a wire transfer of funds between your bank and your supplier’s bank.
-------------------------------------
10)What is diff between Supplier and Vendor?

-------------------------------------

11)Where the Accounting entries will be stored? 
ap_ae_headers_all,ap_ae_lines_all
------------------------------------
12)What are the Interfces we have in AP module?
Ans
 Vendor conversion / Import
A vendor is any company or person that we buy goods or services from.
Interface table --- we need to create our own custom interface table.
Base tables
PO_VENDORS (Segment1-Vendor number is unique)
PO_VENDOR_CONTACTS
PO_VENDOR_SITES_ALL
b) Legacy Invoice
Interface Tables
AP_INVOICE _INTERFACE is the header info
AP_INVOICES_LINES_INTERFACE is the lines table.

RUN PAYABLES INVOICE IMPORT CONCURRENT PROGRAM
Base Tables
AP_INVOICE_ALL is also the header information is stored.
AP_INVOICES_DISTRIBUTIONS_ALL is the lines information table.
AP_PAYMENTS_SCHEDULES_ALL (AMOUNT_REMAINING stores balance amount to be paid)
AP_INVOICE_PAYMENTS_ALL (INVOICE_ID)
AP_CHECKS_ALL (CHECK_ID)

AP_INTERFACE_REJECTIONS (REJECT_LOOKUP_CODE stores the error occurred during the invoice import ex: account required, invalid supplier site )
-------------------------------------------------------
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
AP_INTERFACE_CONTROLS
————————————–
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
AP_SUPPLIER_INT_REJECTIONS
-------------------------------------

 

concurertprocessing

 

1)how will you develop report in Oracle apps?
2)what is Concurrent Program?
3)what is Request group?
4)What is Data Group?
5)What is Token?
6)How to make Parameter as Mandatory?
7)What are the Default types we have?
8)How many value sets we have?What are that?
9)What is :$FLEX$ and :$PROFILE$?
10)what is Dependent and Table Value set/
11)what is Incompatibility?
12)Can we delete concurrent Program?
14)What are the Who coumns?
15)How many types of Execution methods we have?
16)why we will not use TOKEN for PL/SQL Procedure?
17)Can we delete attached Responsibility to the User?
18)Whata re Concurrent Managers available?
19)What is US Folder?
20)What is 11.5.0 Folder?
21)Where do we keep the .rdf inside of Server?
22)What is Executable Short Name?
23)what is CUS_TOP ? What is the advantage of this?

 

glinterface

 

1)how  many days GL Interface taken to upload? 10
2)Waht are the Interface tables?
3)What are the Base tables?
4)what process you have followd?
5)What are the Error tables?
6)What is the standard concurrent Program to upload data?
7)What are the Parameters we need to give while running Jornal Import?
8)What are the Validations you have done?
9)whata are the tables you have come Across?
10)what are the Mandatory columns in Interface table?
11)What are the setups needs to e done before GL Interface?
12)How to Post the Jornals?and How to Correct and Delete the journals?
13)What are the Errors you have faced while runnig GL Import?

EP04    This date is not a business day.
EP02    This set of books does not have any open or future enterable periods
EC08    Invalid currency code.
EB03    The budget year is not open.
EE02    Invalid or disabled encumbrance type.
EM03    Invalid set of books id.
EM26    Invalid originating company.

 

interface

 

1)What process you have folllowed to develop Inbound Interface?
2)What are  the Interface tables?
3)What are the Base tables?
4)What is the Standard program has used to import data?
5)How to track the errors in Interfae?
6)What are the Validations you have done?
7)Tell me some of teh errors which you have faced?
8)Have you used "Autonomous Transaction" in Interface?
9)What is the diff between Inbound and Outbound?
10)Wether you have used UTL_FILE or SQL*loader?
11)What is format of Data file ? (.csv)
12)Wether your Interface is scheduled or manuvally running ? (Scheduled)
14)what is the Differenc between Converison and Interface?

 

inv

 

1)What is Master Item?
2)what is Onhand and Available Qty?
3)What is Move Orders?
4)What are the Inventory Organizations we have?  -suninventories,stores,shop floors
5)What are the KFF we have?
6)Tell me some of the Base tables in Inventory Module?
Interface Tables : MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE (Oracle Defaults)
MTL_ITEM_CATEGORIES_INTERFACE (Oracle has Provided certain default categories, if not specified)
MTL_INTERFACE_ERRORS
Base Tables : MTL_SYSTEM_ITEMS_B (Segment 1 stores Model # of Item)
MTL_ITEM_ATTRIBUTES, MTL_ITEM_CATEGORIES, MTL_ITEM_LOCATIONS, MTL_ITEM_REVISIONS
MTL_ITEM_STATUS (Status Active/Engineer etc)
MTL_ITEM_SUB_INVENTORIES
-----------------------------------------------

7)In which column item will be stored?  
Segment1
-----------------------------------------------
8)What is the Primary key in MTL_SYSTEM_ITEMS_B table?  
Inventory_item_id
-----------------------------------------------
9)In which table we an find out Master Organizations? 
 mtl_parameters
----------------------------------------------
10)In which table we can find out Subinventories?  
 mtl_secondary_inventories
----------------------------------------------
11)In which column we can find out Item category name ?
segment1,segment2
----------------------------------------------
12)What is ABC analysis and ATP date?
13)what are the Item Transactions we have? 
14)What are the reports you have developed or Customized in Inventory?

 

 

invquestions

 

1)what is On-hadn and Available qty?
2)what are the flex fields available
3)what are the tables you have used while developeing inv reports?
4)What is the table name for items?in which column item will be stored?
5)Whata is categorey?
6)where the category will be stored?
7)what is Move orders?
8)how will you find out the Master organizations?
9)What is the table name for Material Transdactions?
10)what is the Diff between master Item and Organization Item
11)what are Item Attributes we have?

 

multiorg

 

1)WHAT IS MULTIORG?
Multiorg is nothing but under single oracle
application implementation maintaining multiple organizations and multiple set of books.
We will have Multiorg tables (table which is ending with _ALL). When ever we enter the
data, which will be stored in the Multiorg tables only.
Ex: OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
OE_ORDER_HOLDS_ALL
PO_HEADER_ALL
PO_LINES_ALL
------------------------------
2)WHAT IS APPLICATION HIRARCHY OR MULTIORG HIRARCHCY?
Business groups
     |
Set Of Books ( Ledger)
     |
legal Entities
     |
Operating Units
     |
Inventory organization
     |
Sub inventories
     |
locators
------------------------------------
3)What is Legal Entity?
It is a legal company for which we prepare physical or tax reports. We
will assign tax identifiers and other legal information.
------------------------------------
4)What is Operqting Unit?
It is one of the organizations where exactly company is being sales,
purchasing payments and receiving and so on. It may be sales office or a division or a
department. At operating unit level PO, AP, AR, OM modules will be implemented.
------------------------------------
What is Set Of Books (Ledger)

It is a financial reporting entity uses a particular chart of accounts
functional currency and accounting calendar. Oracle GL secures transaction information
like general entries balances by set of books. When we assign to the oracle GL
responsibilities there will be a set of books assignment. We can see the information for
that set of book only. The navigation in that responsibility is
Setup [1] Financials [1] Books [1] Define.
-------------------------------------
What is Inventory Organization: It is an organization where we are going to maintain
manufacturing information and items information. It captures inventory transactions,
balances. Oracle inventory, WIP, BOM will be implemented at inventory organization
level.
--------------------------------------
What is Sub Inventory:
Sub inventory is nothing but stocks or goudons or warehouses, which
will be define under inventory organization.
--------------------------------------
What is Locators:
Stock Locators is nothing but the specific location inside the sub inventory
where we will place the item.
--------------------------------------
5)What is the diff between ORG-ID AND ORGANIZATION_ID?
--------------------------------------
6)In which table we can find all the Operating Units?
--------------------------------------
7)In which table we can find all the Inventory organizations?
--------------------------------------
8)Through which profile system will find out your Org_id?
MO:Operating unit
--------------------------------------
9)what is Client -info?
--------------------------------------
10)How can you Implement Multiorg in Reports?
--------------------------------------
11)How can you Implement Multiorg in sql/plsql Programs?
fnd_client_info.set-org_context
----------------------------------
12)How will you find out wether multiorg set up implemented succesfully or not?
select multiorg_flag from fnd-product_groups
----------------------------------
13)Which user exit will capture the User profiles? 
FND SRWINIT
------------------------------------
14)What is Multi Org table (Or) _ALL table?
------------------------------------
15)What is Multi org view?
------------------------------------
16)while developing programs,forms and Reports wether we will use Multi org tables or Multiorg views?

 

po

 

1)How to Find the PO number if we give requisition Number?
po_distributions  table join with req table

2)What is Match Approval and Auto create?
3)How to set up the Buyer?
4)what are the diff types of pO's
5)what is the diff between BLANKET AND standarad?
6)how to attach CONTRACT PO to the STandard PO?

7)What are the TYPES of RFQ's we have?
8)Type of Recipts?
9)Why there is no _ALL for Po_vendors table?

Vendor is common for all the Organization , vendor is not organization specific


10)Why there is no _ALL for Receipts tables?
Receipts will come at Inventory Organization level becuase we  are receiving matertial we will create the receipt receiving material meaans either godown
or inventory or Warehouse all these organizations are IO Level


12)Which table will get effect in Inv when we receive the receipt in Inventory?
MTL_MATERIAL_TRANSACTIONS
MTL-ON_HAND_QUANTITIES_DETAILS

13)can we create pO with out Requisition no? Yes

14)How can we identify the Requision no for a perticular PO from front end?

In the Distributions Tab we have the field called requisition no.

 

sqlloader

 

1)Extensions of all files? data file(.csv), control file(.ctl), bad file(.bad), discard file(.dis), log file( .log).
2)Control file syntax? load data infile* insert into table emp fileds terminated by ','
3)How to   issue commit issue inside of control file? using ROWS command
4)what are the modes or Paths we have what is the diff between those paths?Direct mode and conventional mode
5)How to insert data into multiple tables at a time? by using WHEN command
6)How to insert data into from multiple file  at a time?
7)How to ignore a perticulre column?  FILLER
8)how to mention discard file path in control file?  DISCARD FILE
9)Where do we will keep Control file ?   BIN
10)How to pass parameters to Control file? '&1'
11)What are the different loads we have? -insert, append, truncate
12)What is Trailing Nullcols? Inserting NULL columns if no data is there and we caan't use the DDL, DML,TCL commands in control file.
13)How to register SQl*Loader file as Concurrent Program? Exection Method name=SQL*LOADER
14)how to issue where clause inside of Control file? WHEN COLUMNNAME = 'ASFD'
15)What is Data and Discard files? Rejected records which are rejected by the control file.