Wednesday, October 15, 2014

Oracle Interfaces(ITEMS,QOH,PO,PO REQ,AP,SO) and APIs(HRMS)


INTERFACES
------------------------------------------ITEMS-------------------------------------------
Single Insert:
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE (SEGMENT1,
                                        DESCRIPTION,
                                        ORGANIZATION_ID,
                                        TRANSACTION_TYPE,
                                        PROCESS_FLAG)
     VALUES (‘ELDITEMTEST3’,
             ‘ELDchange’,
             204,
             ‘CREATE,
             1);
If Record is successfully inserted into base tables PROCESS_FLAG will be set to 7. If Record is error out then PROCESS_FLAG will be set to 3 in the interface table.
Error Table : MTL_INTERFACE_ERRORS
Base Table  : MTL_SYSTEM_ITEMS_B
Identification:
Select * from MTL_SYSTEM_ITEMS_B where SEGMENT1=’ ELDITEMTEST3’ and ORGANIZATION_ID=204;    (SEGMENT1.MTL_SYSTEM_ITEMS_INTERFACE                                                                                                                                                                      ORGANIZATION_ID. MTL_SYSTEM_ITEMS_INTERFACE)       
Transaction Type: Create, Update and SYNC
Create  :    Create New Record.
Update :    Update Existing Records.
SYNC     :    Create if item is not existed, if item existed then update.
Process Flag:
1   Picking
2   Assign complete
3   Validation Failed
4  Validated successfully (Validate Items: Yes & Process Items: No)
        7  Successfully imported
Assign complete (2):
Program will pick the records with PROCESS_FLAG=1 and it will change the PROCESS_FLAG to 2 to validate the records. So we can’t see this one (2).
For example, if we import the items Validate Items: Yes, Process Items: Yes then after validation success it will change to 4 and insert into base tables. If insertion is successful then PROCESS_FLAG will be set to 7. In this case we can see the PROCESS_FLAG with 7 instead of 4.
Validations:
Organization_Name          :      Organization_name.ORG_ORGANIZATION_DEFINITIONS
Master_Organization_id  :      Organization_id.MTL_PARAMETERS
Inventory_item_id             :      Segment1 and Organization_id.MTL_SYSTEM_ITEMS_B
Standard Program:
Inventory, Vision Operationsà Itemsà Importà Import Items
Parameters:
All Organizations:
Yes : Run the Interface for all Organizations
No  : Run the Interface only for the organization that you are currently in (Front end)
Validate Items:    and    Process Items:
Yes, No :  Validate the data but won’t import the data into Base tables.
                  PROCESS_FLAG=4 if validation is success.
No, Yes :  Don’t validate the data but import the data into Base tables.
If we already validated the data (PROCESS_FLAG=4), then it won’t validate the data but Process the data.
*Directly if we insert the record with PROCESS_FLAG=4, it won’t process the record. We need to validate the record through the Program only
Delete Processed Rows:
Yes : Delete the successfully Processed Records from the Interface tables.
No  : Don’t delete the records from Interface table.
Process  set:
Null : Pick all the records.
Enter the number similar to SET_PROCESS_ID to pick the particular records
Create or Update Items:
1: Create New Record.
2: Update Existing Records.
3: Create if item is not existed, if item existed then update.
---------------------------------------ON HAND QUANTITY-----------------------------------------
Single Insert:
INSERT INTO MTL_TRANSACTIONS_INTERFACE (ORGANIZATION_ID,
                                        TRANSACTION_DATE,
                                        TRANSACTION_TYPE_ID,
                                        INVENTORY_ITEM_ID,
                                        SUBINVENTORY_CODE,
                                        TRANSACTION_UOM,
                                        TRANSACTION_QUANTITY,
                                        DISTRIBUTION_ACCOUNT_ID,
                                        SOURCE_CODE,
                                        SOURCE_LINE_ID,
                                        SOURCE_HEADER_ID,
                                        PROCESS_FLAG,
                                        TRANSACTION_MODE,
                                        LAST_UPDATE_DATE,
                                        LAST_UPDATED_BY,
                                        CREATION_DATE,
                                        CREATED_BY)
     VALUES (204,
             '01 - MAR - 2005',
             42,
             1751,
             'Stores',
             'Ea',
             100,
             12945,
             123,
             1234,
             12345,
             1,
             3,
             SYSDATE,
             1011883,
             SYSDATE,
             1011883);
If Record is successfully inserted into base tables then record will be deleted from interface tables. If Record is error out then PROCESS_FLAG will be set to 3 in the interface table.
Error Table : MTL_INTERFACE_ERRORS
Base Table  : MTL_MATERIAL_TRANSACTIONS
Identification:
Select * from MTL_MATERIAL_TRANSACTIONS where SOURCE_CODE=123 and SOURCE_LINE_ID=1234                                       
(SOURCE_CODE.MTL_TRANSACTIONS_INTERFACE                                   SOURCE_LINE_ID. MTL_TRANSACTIONS_INTERFACE)
Transaction Mode:
2     Concurrent
3     Background
Concurrent: Transaction Manager will pick the records and assign to Transaction Worker.
Unless Transaction Manager is running it won’t pick these records. Status of         Material Transaction should be Active.
Background: We can launch the Transaction Worker Manually. The Transaction Manager won’t pick these records.
Validations:
Organization_Name         :  Organization_name.ORG_ORGANIZATION_DEFINITIONS
Inventory_item                 :  Segment1 and organization_id.MTL_SYSTEM_ITEMS_B
Transaction_Type             :  Transaction_type_name.MTL_TRANSACTION_TYPES
Subinventory_code          :  Secondary_inventory_name.MTL_SECONDARY_INVENTORIES
Transaction_Uom             :  Unit_of_Measure.MTL_UNITS_OF_MEASURE_TL
Transaction_Quantity      :  should be greater than zero
Distribution_account       :  Segment1…5.GL_CODE_COMBINATIONS
Standard Program:
Inventory, Vision Operationsà Setupà Interface Manager
Select ‘Material transaction’ go to ‘Tools’à Launch
It will launch the program ‘Process transaction interface
Run this program again this will call the program ‘Inventory transaction worker
------------------------------------------INVOICES-------------------------------------------
Single Insert:
INSERT INTO AP_INVOICES_INTERFACE (INVOICE_ID,
                                   INVOICE_NUM,
                                   INVOICE_TYPE_LOOKUP_CODE,
                                   VENDOR_ID,
                                   VENDOR_SITE_ID,
                                   INVOICE_AMOUNT,
                                   INVOICE_CURRENCY_CODE,
                                   SOURCE,
                                   GROUP_ID,
                                   TERMS_ID)
     VALUES (88888,
             'INV - 8888',
             'STANDARD',
             24935,
             5044,
             1000,
             'USD',
             'INTERCOMPANY',
             'TESTCASE1',
             10234);
INSERT INTO AP_INVOICE_LINES_INTERFACE (INVOICE_ID,
                                        INVOICE_LINE_ID,
                                        LINE_NUMBER,
                                        LINE_TYPE_LOOKUP_CODE,
                                        AMOUNT,
                                        DIST_CODE_COMBINATION_ID)
     VALUES (88888,
             99999,
             1,
             'ITEM',
             1000,
             13805);
If the record is loaded successfully into base tables then
        STATUS.AP_INVOICES_INTERFACE is PROCESSED otherwise
        STATUS.AP_INVOICES_INTERFACE is REJECTED
Error Table : AP_INTERFACE_REJECTIONS
Base Table  : AP_INVOICES_ALL     and        AP_INVOICE_DISTRIBUTIONS_ALL
Identification:
Select * from AP_INVOICES_ALL where INVOICE_NUM=’INV-8888’
                                                                      (INVOICE_NUM.AP_INVOICES_INTERFACE)
Validations:
Invoice_type_lookup_code :  Lookup_code.AP_LOOKUP_CODES
                                                          (lookup_type = ’invoice_type’)
Vendor_name                        :  Vendor_name.PO_VENDORS
Vendor_site_code                 :  Vendor_site_code.PO_VENDOR_SITES_ALL
Invoice_currency_code        :  Currency_code.FND_CURRENCIES
Source                                      :  Lookup_code.AP_LOOKUP_CODES (lookup_type=’source’)
Terms_name                           :  Name.AP_TERMS_TL
Line_type_lookup_code        :  Lookup_code.AP_LOOKUP_CODES
                                                        (Lookup_type =  ‘invoice distribution type’)
Code_combinations               :  Segment1…5.GL_CODE_COMBINATIONS
Standard Program:
Payables, Vision Operations(USA)à Payables Open Interface Import
Parameters:
Source :   Enter the source which is mentioned in interface table
Group  :   To Group the Records
-----------------------------------------PURCHASE ORDERS-----------------------------------------
Single Insert:
INSERT INTO PO_HEADERS_INTERFACE (ACTION,
                                  INTERFACE_HEADER_ID,
                                  DOCUMENT_TYPE_CODE,
                                  DOCUMENT_NUM,
                                  ORG_ID,
                                  VENDOR_ID,
                                  VENDOR_SITE_ID,
                                  VENDOR_CONTACT_ID,
                                  SHIP_TO_LOCATION_ID,
                                  BILL_TO_LOCATION_ID,
                                  CURRENCY_CODE,
                                  AGENT_ID)
     VALUES ('ORIGINAL',
             123,
             'STANDARD',
             7777,
             204,
             600,
             1414,
             952,
             2005,
             204,
             'USD',
             25)
INSERT INTO PO_LINES_INTERFACE (INTERFACE_HEADER_ID,
                                INTERFACE_LINE_ID,
                                LINE_NUM,
                                LINE_TYPE_ID,
                                ITEM_ID,
                                ITEM_DESCRIPTION,
                                UNIT_OF_MEASURE,
                                UNIT_PRICE,
                                SHIPMENT_NUM,
                                SHIP_TO_ORGANIZATION_ID,
                                SHIP_TO_LOCATION_ID,
                                QUANTITY)
     VALUES (123,
             1234,
             1,
             1,
             11747,
             'MARTIN DECKER RECORD-O-GRAPH 7 PEN',
             'Each',
             1000,
             1,
             204,
             2490,
             10)
INSERT INTO PO_DISTRIBUTIONS_INTERFACE (INTERFACE_HEADER_ID,
                                        INTERFACE_LINE_ID,
                                        INTERFACE_DISTRIBUTION_ID,
                                        DISTRIBUTION_NUM,
                                        QUANTITY_ORDERED,
                                        CHARGE_ACCOUNT_ID)
     VALUES (123,
             1234,
             12345,
             1,
             10,
             13401)
If the record is loaded successfully into base tables then
        PROCESS_CODE.PO_HEADERS_INTERFACE is ACCEPTED otherwise
        PROCESS_CODE.PO_HEADERS_INTERFACE is REJECTED
Error Table : PO_INTERFACE_ERRORS
Base Table  : PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL    and                                                    PO_DISTRIBUTIONS_ALL
Identification:
Select * from PO_HEADERS_ALL where SEGMENT1=’7777’
                                                                      (DOCUMENT_NUM.PO_HEADERS_INTERFACE)
Validations:
Document_type                           :  Document_sub_type.PO_DOCUMENT_TYPES
Org_name                                     :  Name.HR_OPERATING_UNITS
Vendor_name                              :  Vendor_name.PO_VENDORS
Vendor_site                                  :  Vendor_site_code.PO_VENDOR_SITES_ALL
Vendor_contact_name              :  Last_name, first_name.PO_VENDOR_CONTACTS
Ship_to_location_name             :  Location_code.HR_LOCATIONS
Bill_to_location_name               :  Location_code.HR_LOCATIONS
Currency_code                             :   Currency_code.FND_CURRENCIES
Agent_name                                 :   Agent_name.PO_AGENTS_V
Line_type                                       :   Line_type.PO_LINE_TYPES_TL
Item_name, Org_id                     :   Segment1, Organization_id.MTL_SYSTEM_ITEMS_B
Unit_of_measure                         :   Unit_of_Measure.MTL_UNITS_OF_MEASURE_TL
Ship_to_organization_name     :   Organization_name.ORG_ORGANIZATION_DEFINITIONS
Ship_to_location_name             :   Location_code.HR_LOCATIONS
Charge_account_code                :  Segment1…5.GL_CODE_COMBINATIONS
Standard Program:
Purchasing, Vision Operationsà Import Standard Purchase Orders
------------------------------------------REQUISITIONS-------------------------------------------
Single Insert:
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (INTERFACE_SOURCE_CODE,
                                           ORG_ID,
                                           REQUISITION_TYPE,
                                           PREPARER_ID,
                                           LINE_TYPE_ID,
                                           ITEM_ID,
                                           ITEM_DESCRIPTION,
                                           UNIT_OF_MEASURE,
                                           QUANTITY,
                                           UNIT_PRICE,
                                           NEED_BY_DATE,
                                           DESTINATION_TYPE_CODE,
                                           AUTHORIZATION_STATUS,
                                           SOURCE_TYPE_CODE,
                                           DESTINATION_ORGANIZATION_ID,
                                           DELIVER_TO_LOCATION_ID,
                                           DELIVER_TO_REQUESTOR_ID,
                                           CHARGE_ACCOUNT_ID)
     VALUES (123,
             204,
             ‘PURCHASE’,
             25,
             1,
             6068,
             ‘PrinterMechanism’,
             ‘Each,
             10,
             100,
             SYSDATE + 2,
             ‘INVENTORY’,
             ‘INCOMPLETE’,
             ‘VENDOR’,
             204,
             204,
             25,
             13401)
INSERT INTO PO_REQ_DIST_INTERFACE_ALL (INTERFACE_SOURCE_CODE,
                                       DISTRIBUTION_NUMBER,
                                       QUANTITY,
                                       CHARGE_ACCOUNT_ID)
     VALUES (123,
             1,
             10,
             13401)
If the record is loaded successfully into base tables then
        Record will be deleted from Interface tables otherwise
        PROCESS_CODE.PO_REQUISITIONS_INTERFACE_ALL is ERROR
Error Table : PO_INTERFACE_ERRORS
Base Table  : PO_REQUISITION_HEADERS_ALL, PO_REQUISITION_LINES_ALL    and                                                   PO_REQ_DISTRIBUTIONS_ALL
Identification:
Select * from PO_REQUISITION_HEADERS_ALL where INTERFACE_SOURCE_CODE=’123’
                                             (INTERFACE_SOURCE_CODE.PO_REQUISITIONS_INTERFACE_ALL)
Validations:
Org_name                                      :  Name .HR_OPERATING_UNITS
Requisition_type                           :  Document_sub_type.PO_DOCUMENT_TYPE
Preparer_name                              :  Agent_name.PO_AGENTS_V
Requestor_name                           :  Agent_name.PO_AGENTS_V
Line_type                                         :  Line_type.PO_LINE_TYPES_TL
Item_name, Org_id                        :   Segment1, Organization_id.MTL_SYSTEM_ITEMS_B
Unit_of_measure                            :   Unit_of_Measure.MTL_UNITS_OF_MEASURE_TL
Destination_organization_name  :  Organization_name.ORG_ORGANIZATION_DEFINITIONS
Deliver_to_location                         :  Location_code.HR_LOCATIONS
Charge_account_code                     :  Segment1…5.GL_CODE_COMBINATIONS
Standard Program:
Purchasing, Vision Operationsà Requisition Import
Parameters:
Multiple Distributions:
  Yes :   We can give multiple distributions for one line
  No  :   Only one distribution will be created for one line based on the CHARGE_ACCOUNT_ID given n the PO_REQUISITIONS_INTERFACE_ALL (Line)
Initial Approval after ReqImport:
  Yes :   Requisition will be approved
  No  :   Requisition will not be approved
------------------------------------------SALES ORDERS-------------------------------------------
Single Insert:
INSERT INTO OE_HEADERS_IFACE_ALL (ORDER_SOURCE_ID,
                                  ORIG_SYS_DOCUMENT_REF,
                                  ORG_ID,
                                  ORDERED_DATE,
                                  ORDER_TYPE_ID,
                                  PRICE_LIST_ID,
                                  TRANSACTIONAL_CURR_CODE,
                                  SALESREP_ID,
                                  SOLD_TO_ORG_ID,
                                  SHIP_FROM_ORG_ID,
                                  SHIP_TO_ORG_ID,
                                  INVOICE_TO_ORG_ID,
                                  CLOSED_FLAG,
                                  BOOKED_FLAG,
                                  CREATED_BY,
                                  CREATION_DATE,
                                  LAST_UPDATED_BY,
                                  LAST_UPDATE_DATE)
     VALUES (0,
             ‘5 - H’,
             204,
             SYSDATE,
             1437,
             1000,
             ‘USD’,
             -3,
             1290,
             204,
             1425,
             1424,
             ‘N’,
             ‘N’,
             1007932,
             SYSDATE,
             1007932,
             SYSDATE)
INSERT INTO OE_LINES_IFACE_ALL (ORDER_SOURCE_ID,
                                ORIG_SYS_DOCUMENT_REF,
                                ORIG_SYS_LINE_REF,
                                LINE_NUMBER,
                                ORG_ID,
                                INVENTORY_ITEM_ID,
                                ORDERED_QUANTITY,
                                ORDER_QUANTITY_UOM,
                                SHIP_FROM_ORG_ID,
                                SOLD_TO_ORG_ID,
                                INVOICE_TO_ORG_ID,
                                SHIP_TO_ORG_ID,
                                SALESREP_ID,
                                UNIT_LIST_PRICE,
                                UNIT_SELLING_PRICE,
                                CREATED_BY,
                                CREATION_DATE,
                                LAST_UPDATED_BY,
                                LAST_UPDATE_DATE,
                                LINE_TYPE_ID,
                                CLOSED_FLAG,
                                PRICE_LIST_ID)
     VALUES (0,
             ‘5 - H’,
             ‘5 - L’,
             1,
             204,
             149,
             2,
             ‘Ea’,
             204,
             1290,
             1424,
             1425,
             3,
             1599,
             1599,
             1007902,
             SYSDATE,
             1007902,
             SYSDATE,
             1427,
             ‘N’,
             1000)
Closed_flag: The order will be in ‘closed’ status if it is ‘Y’. (irrespective of BOOKED_FLAG)
Booked flag: The order will be in ‘entered’ status if it is ‘N’ (CLOSED_FLAG should be ‘N’)
                   The order line status will be in ‘Awaiting shipping’,
                   Header status will be in ‘booked’  if it is ‘Y’ (CLOSED_FLAG should be ‘N’)
Error Table :  No Error Table. Output or  log file will display the errors.
Base Table  :  OE_ORDER_HEADERS_ALL   and    OE_ORDER_LINES_ALL
Identification:
Select * from OE_ORDER_HEADERS_ALL where ORIG_SYS_DOCUMENT_REF=’5-H’
                                          (ORIG_SYS_DOCUMENT_REF.OE_HEADERS_IFACE_ALL)
Validations:
Order_source                        : Name.OE_ORDER_SOURCES         
Org_name                              : Name.HR_OPERATING_UNITS      
Order_type                            : Name.OE_TRANSACTION_TYPES_TL             
Price_list                                : Name.QP_LIST_HEADERS      
Transactional_curr_code    : Currency.FND_CURRENCIES   
Salesrep_name                     : Name.RA_SALESREPS_ALL       
Sold_to_organization          : Cust_account_id.HZ_CUST_ACCOUNTS    
Ship_from_organization     : SHIP_FROM_ORG_ID should exists in ORG_ORGANIZATION_DEFINITIONS ood, MTL_PARAMETERS MTL tables. This should be under the Operating unit (Org_id)
Ship_to_organization          : Site_use_id.HZ_CUST_SITE_USES_ALL            
Invoice_to_organization     : Site_use_id.HZ_CUST_SITE_USES_ALL      
Inventory_item, Org_id       : Segment1, Organization_id.MTL_SYSTEM_ITEMS_B        
Line_type                               : Name.OE_TRANSACTION_TYPES_TL          
Unit_of_Measure                 : Unit_of_Measure.MTL_UNITS_OF_MEASURE_TL         
Standard Program:
Order Management, Vision Operationsà Orders, Returnsà Import OrdersàOrder Import Requestà ‘Order Import’ program
             Above program will call ‘Order Import Child Req1 (Order Import)’ another child program.
API-Application Programming Interface
Any API can return 3 types of information:
  1. Return Status
  2. Messages describing the operations Performed/Errors encounter by the API.
  3. Other Output values  EX: Party_id, Customer_id  and  party_site_id etc.
Return Status:
Success: The API was able to perform all the operations requested by the user.
Error:  The API was not able to perform all the operations or some of the operations requested                                                           by the user.
Unexpected Errors:  The API has encountered an error condition which it didn’t expect.
Examples:  Memory Errors and Programming Errors (Division by Zero).
In Most of the cases Developers can’t correct Unexpected Errors. System Administrators can handle these errors.
Messages:
The API put messages into message list. Programs calling these APIs can then retrieve the messages for display purpose or loading into the tables. In order to store the data into Message List we need to set P_INT_MSG_LIST to true.
Parameters:
P_API_VERSION: Used to compare version numbers of user given and current version number.
P_COMMIT: API does the commit instead of user.
P_INT_MSG_LIST: API initializes the message list instead of user.
P_VALIDATION_LEVEL: It determines whether API has to do validations or not.
        FND_API.G_VALID_LEVEL_NONE API  --won’t do any validations
        FND_API.G_VALID_LEVEL_FULL  API  --will do full validations
P_VALIDATE:
When set to ‘False’ (Default Value), the procedure does all the validations. If operation is valid, the rows in the corresponding table will be inserted or deleted or updated depends upon the requirement. Any non warning OUT parameters (Ex: P_PERSON_ID), Warning OUT parameters (Ex: I_NAME_COMBINATION_WARNING) and INOUT parameters are all set with specific values.
When set to ‘True’, API checks only whether operation is valid or not. It does so by issuing a savepoint at the start of the procedure and rolling back to that savepoint at the end. We can’t access these internal savepoints. If the procedure is successful without any errors, non warning OUT parameters (Ex: P_PERSON_ID) are set to null, warning OUT parameters (Ex: I_NAME_COMBINATION_WARNING) are set to null and INOUT parameters are set with IN values.
Object Version Number:
Every row in the table is assigned to an Object Version Number. When a new row is inserted, API assign the number ‘1’ (one) to this. Whenever row is updated in the table Object Version Number is incremented.
------------------------------------------EMPLOYEE-------------------------------------------
DECLARE
   v_last_name                   VARCHAR2 (90) := 'API - EMP10';
   v_sex                         VARCHAR2 (10) := 'M';
   v_person_type_id              NUMBER := 13;
   v_national_identifier         VARCHAR2 (90) := '134 - 9089 - 09';
   v_date_of_birth               DATE := TO_DATE ('12-mar-1984', 'dd-mm-yyyy');
   v_business_group_id           NUMBER := 202;
   v_hire_date                   DATE := TO_DATE ('21-jan-2009', 'dd-mm-yyyy');
   I_employee_number             VARCHAR2 (90) := '123';
   I_person_id                   NUMBER;
   I_assignment_id               NUMBER;
   I_per_object_version_number   NUMBER;
   I_asg_object_version_number   NUMBER;
   I_per_effective_start_date    DATE;
   I_per_effective_end_date      DATE;
   I_full_name                   VARCHAR2 (240);
   I_per_comment_id              NUMBER;
   I_assignment_sequence         NUMBER;
   I_assignment_number           VARCHAR2 (30);
   I_name_combination_warning    BOOLEAN;
   I_assign_payroll_warning      BOOLEAN;
   I_orig_hire_warning           BOOLEAN;
   v_user_id                     NUMBER;
   v_resp_id                     NUMBER;
   v_resp_appl_id                NUMBER;
BEGIN
   v_user_id := fnd_global.user_id;
   v_resp_id := fnd_global.resp_id;
   v_resp_appl_id := fnd_global.resp_appl_id;
   FND_GLOBAL.APPS_INITIALIZE (v_user_id, v_resp_id, v_resp_appl_id);
   HR_EMPLOYEE_API.CREATE_EMPLOYEE (
      p_validate                    => FALSE,
      p_hire_date                   => v_hire_date,
      p_business_group_id           => v_business_group_id,
      p_last_name                   => v_last_name,
      p_sex                         => v_sex,
      p_person_type_id              => v_person_type_id,
      p_natioal_identifier          => v_natioal_identifier,
      p_date_of_birth               => v_date_of_birth,
      p_employee_number             => I_employee_number,
      p_person_id                   => I_person_id,
      p_assignment_id               => I_assignment_id,
      p_per_object_version_number   => I_per_object_version_number,
      p_asg_object_version_number   => I_asg_object_version_number,
      p_per_effective_start_date    => I_per_effective_start_date,
      p_per_effective_end_date      => I_per_effective_end_date,
      p_full_name                   => I_full_name,
      p_per_comment_id              => I_per_comment_id,
      p_assignment_sequence         => I_assignment_sequence,
      p_assignment_number           => I_assignment_number,
      p_name_combination_warning    => I_name_combination_warning,
      p_assign_payroll_warning      => I_assign_payroll_warning,
      p_orig_hire_warning           => I_orig_hire_warning);
   DBMS_OUTPUT.put_line ('I_employee_number    ' || I_employee_number);
   DBMS_OUTPUT.put_line ('I_person_id               ' || I_person_id);
   COMMIT;
END;
Base Tables:  PER_ALL_PEOPLE_F
Identification:
Select * from PER_ALL_PEOPLE_F where PERSON_ID=I_PERSON_ID;
Validations:
Business group                           :  Name.PER_BUSINESS_GROUPS
Sex                                                :  Sex.HR_LOOKUPS (lookup_type=’sex’)
Person Type, Business Group  :  User_person_type, Business_group_id.PER_PERSON_TYPES
----------------------------------------EMPLOYEE ADDRESS-----------------------------------------
DECLARE
   v_validate                BOOLEAN;
   v_address_line1           VARCHAR2 (90);
   v_address_line2           VARCHAR2 (90);
   v_address_line3           VARCHAR2 (90);
   v_town_or_city            VARCHAR2 (90);
   v_region_2                VARCHAR2 (90);
   v_postal_code             VARCHAR2 (90);
   v_region_1                VARCHAR2 (90);
   v_country                 VARCHAR2 (90);
   v_effective_date          DATE;
   v_primary_flag            VARCHAR2 (1);
   v_style                   VARCHAR2 (90);
   v_date_from               DATE;
   v_address_id              NUMBER;
   v_object_version_number   NUMBER;
   v__person_id              NUMBER;
   v_user_id                 NUMBER;
   v_resp_id                 NUMBER;
   v_resp_appl_id            NUMBER;
BEGIN
   v_validate := FALSE;
   v_address_line1 := 'API ADDRESS5';
   v_address_line2 := '1-234-5';
   v_address_line3 := 'STREET NO:4';
   v_town_or_city := 'A and M College';
   v_region_2 := 'TX';
   v_postal_code := 77840;
   v_region_1 := 'Brazos';
   v_country := 'US';
   v_effective_date := TRUNC (SYSDATE);                            --date from
   v_primary_flag := 'N';
   v_style := 'US';
   v_date_from := TRUNC (SYSDATE);
   v_person_id := 31488;
   --v_user_id                            :=  fnd_global.user_id;
   --v_resp_id                            :=  fnd_global.resp_id;
   --v_resp_appl_id                  :=  fnd_global.resp_appl_id;
   --FND_GLOBAL.APPS_INITIALIZE (v_user_id, v_resp_id, v_resp_appl_id);
   FND_GLOBAL.APPS_INITIALIZE (1013418, 50559, 222);
   HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS (
      p_validate                => v_validate,
      p_person_id               => v_person_id,
      p_address_line1           => v_address_line1,
      p_address_line2           => v_address_line2,
      p_address_line3           => v_address_line3,
      p_town_or_city            => v_town_or_city,
      p_region_2                => v_region_2,
      p_postal_code             => v_postal_code,
      p_region_1                => v_region_1,
      p_country                 => v_country,
      p_efective_date           => v_efective_date,
      p_primary_flag            => v_primary_flag,
      p_style                   => v_style,
      p_date_from               => v_date_from,
      p_address_id              => v_address_id,
      p_object_version_number   => v_object_version_number);
   DBMS_OUTPUT.put_line ('p_address_id     ' || v_address_id);
   DBMS_OUTPUT.put_line (
      'p_object_version_number   ' || v_object_version_number);
   COMMIT;
END;
Base Tables: PER_ADDRESSES
Identification:
Select * from PER_ADDRESSES where PERSON_ID=31488 and ADDRESS_ID=p_address_id and BUSINESS_GROUP_ID=202;
Validations:
Business Group                             :  Name.PER_BUSINESS_GROUPS
Person name, Business Group   :  Person_name, Business_group_id.PER_ALL_PEOPLE_F
State                                               :  State_name.PAY_US_STATES
State, Country                               :  State_code, Country_name
State, Country, City                      :  State_code, Country_code, City_name
Postal code                                    :  between Code_start.PAY_CA_POSTAL_CODES_V and
                                                                        Code_end.PAY_CA_POSTAL_CODES_V 

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect