11i to R12 Replaced Tables in Oracle apps

Following are the join conditions. Use these to 
complete your query. 

SO_HEADERS_ALL -- OE_ORDER_HEADERS_ALL 
SO_LINES_ALL -- OE_ORDER_LINES_ALL 
so_picking_lines_all -- WSH_DELIVERY_DETAILS 
so_picking_line_details -- wsh_delivery_assignments 


WSH_NEW_DELIVERIES.DELIVERY_IDWSH_DELIVERY_ASS IGNMENTS.DELIVERY_ID 

WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID WSH_DELIVERY_ASSIGNMENTS.DELIVERY_DETAIL_ID 

WSH_DELIVERY_DETAILS.SOURCE_LINE_ID OE_ORDER_LINES_ALL.LINE_ID 

=================================================================================

RA_CUSTOMERS

The table below lists the corresponding HZ table and column for various columns in the current RA_CUSTOMERS view

Column in RA_CUSTOMERS	Corresponding Table	Column
customer_name	hz_parties	party_name,
customer_id	hz_cust_accounts	cust_account_id
customer_number	hz_cust_accounts	account_number
status	hz_cust_accounts	status

Join Conditions:

HZ_CUST_ACCOUNTS..Party_Id = HZ_PARTIES.Party_Id

RA_ADDRESSES

Column in RA_ADDRESSES	Corresponding Table	Column
address_id	hz_cust_acct_sites_all	cust_acct_site_id
status	hz_cust_acct_sites_all	status
address1	hz_locations	address1
address2	hz_locations	address2
address3	hz_locations	address3
address4	hz_locations	address4
city	hz_locations	city
state	hz_locations	state
postal_code	hz_locations	postal_code
county	hz_locations	county
country	hz_locations	country
language	hz_locations	language

Source Tables:  HZ_PARTY_SITES, HZ_LOCATIONS, HZ_CUST_ACCT_SITES_ALL

Join Conditions:

HZ_CUST_ACCT_SITES_ALL.party_site_id = HZ_PARTY_SITES.party_site_id

HZ_LOCATIONS.location_id = HZ_PARTY_SITES.location_id

RA_SITE_USES

The table below lists the corresponding HZ table and column for various columns in the current RA_SITE_USES view

Column in RA_SITE_USES	Corresponding Table	Column
site_use_id	hz_cust_site_uses	site_use_id
site_use_code	hz_cust_site_uses	site_use_code
status	hz_cust_site_uses	status
address_id	hz_cust_site_uses	cust_acct_site_id

RA_CONTACTS

Column in RA_CONTACTS	Corresponding Table	Column
contact_id	hz_cust_account_roles	cust_account_role_id
status	hz_cust_account_roles	status
customer_id	hz_cust_account_roles	cust_account_id
address_id	hz_cust_account_roles	cust_acct_site_id
first_name	hz_parties	person_first_name
last_name	hz_parties	person_last_name

Source Tables: HZ_CUST_ACCOUNT_ROLES, HZ_PARTY_RELATIONSHIPS, HZ_PARTIES

Join Conditions:

HZ_CUST_ACCOUNT_ROLES.party_id = HZ_PARTY_RELATIONSHIPS.party_id

HZ_CUST_ACCOUNT_ROLES.role_type = 'CONTACT'

HZ_PARTIES.party_id = HZ_PARTY_RELATIONSHIPS.subject_id

RA_CONTACT_ROLES

Column in RA_CONTACT_ROLES	Corresponding Table	Column
contact_id	hz_role_responsibility	cust_account_role_id
usage_code	hz_role_responsibility	responsibility_type
primary_flag	hz_role_responsibility	primary_flag

11i Table	R12 Change
ra_addresses_all	SELECT acct_site.cust_account_id customer_id,     acct_site.cust_acct_site_id address_id FROM hz_party_sites party_site,                  hz_loc_assignments loc_assign,                  hz_locations loc,                  hz_cust_acct_sites_all acct_site            WHERE acct_site.party_site_id = party_site.party_site_id              AND loc.location_id = party_site.location_id              AND loc.location_id = loc_assign.location_id              AND NVL (acct_site.org_id, -99) = NVL (loc_assign.org_id, -99)
ra_site_uses_all	SELECT site_use_id, LOCATION, attribute1  FROM hz_cust_site_uses_all
ra_customers	SELECT cust_acct.cust_account_id customer_id,                  SUBSTRB (party.party_name, 1, 50) customer_name,                  cust_acct.account_number customer_number             FROM hz_parties party, hz_cust_accounts cust_acct            WHERE cust_acct.party_id = party.party_id

Suppliers:

New R12 tables  -> Old 11i Tables

AP_SUPPLIERS - replaces PO_VENDORS

AP_SUPPLIER_SITES_ALL- replaces PO_VENDOR_SITES_ALL

Additional supplier related tables in IBY (Payments) and HZ (TCA):

IBY_EXTERNAL_PAYEES_ALL - stores Payee(supplier) information.

HZ_PARTIES - Party data for the suppliers.

HZ_PARTY_SITES - Party site data for the supplier sites.

Invoices:

Additional table in R12: AP_INVOICE_LINES_ALL

Allocations - AP_CHRG_ALLOCATIONS_ALL is obsolete in R12

Taxes:

Functionality provided by E-Business Tax

New tables in R12

ZX_LINES - Detailed Tax lines for the invoice (trx_id = invoice_id)

ZX_LINES_SUMMARY - Summary tax lines for the invoice (trx_id = invoice_id)

ZX_REC_NREC_DIST  - Tax distributions for the invoice (trx_id = invoice_id)

ZX_LINES_DET_FACTORS - Tax determination factors for the invoice (trx_id = invoice_id)

Payments:

Functionality moved to central Payments (IBY)

New IBY tables in R12:

IBY_PAY_SERVICE_REQUESTS  - Payment Process Request information

Accounting:

Functionality moved to SubLedger Accounting (SLA)

New R12 tables:

XLA_EVENTS -> replaces AP_ACOCUNTING_EVENTS_ALL 

XLA_AE_HEADERS -> replaces AP_AE_HEADERS_ALL

XLA_AE_LINES-> replaces AP_AE_LINES_ALL

XLA_DISTRIBUTION_LINKS

Trial Balance:

New R12 Table

XLA_TRIAL_BALANCES

AP_LIABILITY_BALANCE-> not used in new R12 transactions

AP_TRIAL_BALANCE -> not used in new R12 transactions

Bank Accounts:

Functionality moved to Cash Management.

CE_BANK_ACCOUNTS -> replaces AP_BANK_ACCOUNTS_ALL

CE_BANK_ACCT_USES_ALL  -> replaces AP_BANK_ACCOUNT_USES_ALL

CE_PAYMENT_DOCUMENTS -> AP_CHECK_STOCKS_ALL

PROCEDURE TO PRINT LOG INFORMATION FROM BACKEND AND FRONT END IN ORACLE APPS

PROCEDURE TO PRINT THE LOG INFORMATION FROM BACKEND AND FRONT END

PROCEDURE print_log(p_message IN VARCHAR2)
IS

BEGIN
    fnd_file.put_line(fnd_file.LOG,p_message);
    dbms_output.put_line(p_message);
END print_log;

Inventory Miscellaneous Receipt or Miscellaneous issue scripts from backend in oracle apps

Inventory Miscellaneous Receipt or Miscellaneous issue scripts in oracle apps

Step1 : Run the Below Procedure 

create or replace PROCEDURE PKMTL_TRANSACTION(p_item_id           IN   VARCHAR2,
                           p_organization_id   IN   NUMBER,
                           p_subinv            IN   VARCHAR2,
                           p_loc               IN   VARCHAR2,
                           p_lot_no            IN   VARCHAR2,
                           p_uom_code          IN   VARCHAR2,
                           p_quantity          IN   NUMBER,
                           p_transaction_type  IN   VARCHAR2,
                           p_trans_type_ref    IN   VARCHAR2,
                           p_primary_qty       IN   NUMBER,
                           x_return_status     OUT  VARCHAR2,
                           x_msg_count         OUT  NUMBER,
                           x_msg_data          OUT  VARCHAR2)
IS

l_transaction_id        NUMBER;
l_loc_id                        NUMBER;
l_trn_type_id           NUMBER;
l_segment1                      VARCHAR2(150);
l_segment2                      VARCHAR2(150);
l_segment3                      VARCHAR2(150);
l_segment4                      VARCHAR2(150);
l_segment5                      VARCHAR2(150);
l_segment6                      VARCHAR2(150);
l_segment7                      VARCHAR2(150);
l_segment8                      VARCHAR2(150);
l_segment9                      VARCHAR2(150);
l_segment10             VARCHAR2(150);
l_segment11              VARCHAR2(150);
l_expiration_date       DATE;
l_trans_count       NUMBER;
lc_result           NUMBER;
l_subinv_code mtl_item_locations_kfv.subinventory_code%type;
r_mtl_trns_rec mtl_transactions_interface%rowtype;
R_MTL_LOT_REC MTL_TRANSACTION_LOTS_INTERFACE%ROWTYPE;

G_L_TRANSACTION_ID  number;
G_L_LPN_ID          number:=0;

CURSOR cur_tns_rec
IS
SELECT mtt.*
FROM MTL_TRANSACTION_TYPES mtt
where MTT.TRANSACTION_TYPE_NAME = P_TRANSACTION_TYPE;
R_TNS_REC CUR_TNS_REC%ROWTYPE;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
      x_return_status:= null;
      x_msg_data := null;

dbms_output.put_line('================================================================================');
      BEGIN
           SELECT mtl_material_transactions_s.NEXTVAL
           INTO   l_transaction_id
           FROM   DUAL;
      EXCEPTION WHEN OTHERS THEN
          l_transaction_id := NULL;
      END;
      OPEN  cur_tns_rec;
      FETCH cur_tns_rec INTO r_tns_rec;
      CLOSE cur_tns_rec;

      BEGIN
      
      
      --l_segment1:=01;
                 l_segment1:='01';
                 l_segment2:='510';
                 l_segment3:='4110';
                 l_segment4:='0000';
                 l_segment5:='000';
                 l_segment6:=null;
                 l_segment7:=null;
                 l_segment8:=null;
                 l_segment9:=null;
                 l_segment10:=null;
           /*SELECT gcc.segment1,
                 gcc.segment2,
                 gcc.segment3,
                 gcc.segment4,
                 gcc.segment5,
                 gcc.segment6,
                 gcc.segment7,
                 gcc.segment8,
                 gcc.segment9,
                 gcc.segment10
           INTO   l_segment11,
                 l_segment2,
                 l_segment3,
                 l_segment4,
                 l_segment5,
                 l_segment6,
                 l_segment7,
                 l_segment8,
                 l_segment9,
                 l_segment10
           FROM   gl_code_combinations_kfv gcck, gl_code_combinations gcc
           where  GCC.CODE_COMBINATION_ID= 19547
               AND    gcc.code_combination_id = gcck.code_combination_id;*/
          EXCEPTION WHEN OTHERS THEN
                   l_segment1 :=NULL;
                   l_segment2 :=NULL;
                   l_segment6 :=NULL;
                   l_segment3 :=NULL;
                   l_segment4 :=NULL;
                   l_segment5 :=NULL;
                   l_segment7 :=NULL;
                   l_segment8 :=NULL;
                   l_segment9 :=NULL;
                   l_segment10 :=NULL;
          END;

          BEGIN
              SELECT inventory_location_id,subinventory_code
              INTO   l_loc_id,l_subinv_code
              FROM   mtl_item_locations_kfv
              WHERE  concatenated_segments = p_loc
              AND    organization_id = p_organization_id;
          EXCEPTION WHEN OTHERS THEN
                    l_loc_id := NULL;
          END;

          dbms_output.put_line('p_loc: '||p_loc);
          dbms_output.put_line('l_loc_id: '||l_loc_id);

          dbms_output.put_line('Transaction_type: '||r_tns_rec.transaction_type_name);
          dbms_output.put_line('p_quantity: '||p_quantity);
          BEGIN
              r_mtl_trns_rec := NULL;
              g_l_transaction_id := NULL;
              r_mtl_trns_rec.transaction_uom             :=  p_uom_code;
              r_mtl_trns_rec.transaction_date            :=  SYSDATE;
              r_mtl_trns_rec.source_code                 :=  p_transaction_type;
              r_mtl_trns_rec.source_line_id              :=  l_transaction_id; -- 422170
              r_mtl_trns_rec.source_header_id            :=  l_transaction_id ;  -- 167091
              r_mtl_trns_rec.process_flag                :=  1;
              r_mtl_trns_rec.transaction_mode            :=  3;
              r_mtl_trns_rec.lock_flag                   :=  2;
              r_mtl_trns_rec.locator_id                  :=  l_loc_id;
              r_mtl_trns_rec.last_update_date            :=  SYSDATE;
              r_mtl_trns_rec.last_updated_by             :=  fnd_global.user_id;
              r_mtl_trns_rec.creation_date               :=  SYSDATE;
              r_mtl_trns_rec.created_by                  :=  fnd_global.user_id;
              r_mtl_trns_rec.inventory_item_id           :=  p_item_id;
              r_mtl_trns_rec.subinventory_code           :=  nvl(p_subinv,l_subinv_code);
              r_mtl_trns_rec.organization_id             :=  p_organization_id;
              r_mtl_trns_rec.transaction_quantity        :=  p_quantity;
              r_mtl_trns_rec.primary_quantity            :=  p_primary_qty;
              R_MTL_TRNS_REC.TRANSACTION_TYPE_ID         :=  R_TNS_REC.TRANSACTION_TYPE_ID;
              r_mtl_trns_rec.transaction_source_type_id  :=  r_tns_rec.transaction_source_type_id;
              r_mtl_trns_rec.transaction_action_id       :=  r_tns_rec.transaction_action_id;
              r_mtl_trns_rec.dst_segment1                :=  l_segment1;
              r_mtl_trns_rec.dst_segment2                :=  l_segment2;
              r_mtl_trns_rec.dst_segment3                :=  l_segment3;
              r_mtl_trns_rec.dst_segment4                :=  l_segment4;
              r_mtl_trns_rec.dst_segment5                :=  l_segment5;
              r_mtl_trns_rec.dst_segment6                :=  l_segment6;
              r_mtl_trns_rec.dst_segment7                :=  l_segment7;
              r_mtl_trns_rec.dst_segment8                :=  l_segment8;
              r_mtl_trns_rec.dst_segment9                :=  l_segment9;
              r_mtl_trns_rec.dst_segment10               :=  l_segment10;
              r_mtl_trns_rec.transaction_interface_id    :=  l_transaction_id;
              r_mtl_trns_rec.transaction_header_id       :=  l_transaction_id;
              r_mtl_trns_rec.transaction_reference       :=  p_trans_type_ref;
              IF g_l_lpn_id >0 THEN
                 r_mtl_trns_rec.transfer_lpn_id      := g_l_lpn_id;

                 dbms_output.put_line('g_l_lpn_id: '||g_l_lpn_id);
              END IF;

              -- Insert MTI for inv open transactions.
              insert into MTL_TRANSACTIONS_INTERFACE values R_MTL_TRNS_REC;
              dbms_output.put_line('Record inserted into mtl_transactions_interface with transaction_header_id: '||l_transaction_id);
              g_l_transaction_id := l_transaction_id;

              --dbms_output.put_line('Inserted a record'||l_transaction_id);
              x_return_status := fnd_api.g_ret_sts_success;
          EXCEPTION WHEN OTHERS THEN
                 fnd_file.put_line (fnd_file.output,'in exception ---- inserting data into  MTL_TRANSACTIONS_INTERFACE : '|| SQLERRM);
                 dbms_output.put_line('Error'||SQLERRM);
                 x_return_status := fnd_api.g_ret_sts_error;
                 x_msg_count := x_msg_count+1;
                 x_msg_data := 'Error While Inserting data to MTL_TRANSACTIONS_INTERFACE :'||SQLERRM;
          END;

  DBMS_OUTPUT.PUT_LINE('STEP1');
         IF x_return_status != fnd_api.g_ret_sts_error  THEN --Successful return of first insert
            IF p_lot_no IS NOT NULL THEN
               --Derive lot expiration date for the lot number
               BEGIN
                    SELECT expiration_date
                    INTO l_expiration_date
                    FROM MTL_LOT_NUMBERS
                    WHERE lot_number = p_lot_no
                    AND inventory_item_id = p_item_id
                    AND organization_id = p_organization_id;
                    
               EXCEPTION WHEN OTHERS THEN
                 dbms_output.put_line('Exception in fetching lot expiration_date: '||sqlerrm);
                 l_expiration_date:= null;
                 DBMS_OUTPUT.PUT_LINE('STEP2');
               END;
DBMS_OUTPUT.PUT_LINE('STEP3');
               dbms_output.put_line('Expiration_date: '||l_expiration_date);
DBMS_OUTPUT.PUT_LINE('STEP3.1');
               BEGIN
               DBMS_OUTPUT.PUT_LINE('STEP4');
                    --Insert lot number interface if the item is lot controlled.
                    r_mtl_lot_rec := NULL;
                    g_l_transaction_id := l_transaction_id;
                    r_mtl_lot_rec.transaction_interface_id    := l_transaction_id;
                    r_mtl_lot_rec.source_code                 := p_transaction_type;
                    r_mtl_lot_rec.source_line_id              := l_transaction_id;
                    r_mtl_lot_rec.last_update_date            := SYSDATE;
                    r_mtl_lot_rec.last_updated_by             := FND_GLOBAL.USER_ID;
                    r_mtl_lot_rec.creation_date               := SYSDATE;
                    r_mtl_lot_rec.created_by                  := FND_GLOBAL.USER_ID;
                    r_mtl_lot_rec.last_update_login           := FND_GLOBAL.LOGIN_ID ;
                    r_mtl_lot_rec.lot_number                  := p_lot_no;
                    r_mtl_lot_rec.lot_expiration_date         := l_expiration_date;
                    r_mtl_lot_rec.transaction_quantity        := p_quantity   ;
                    r_mtl_lot_rec.primary_quantity            := p_quantity;
                    r_mtl_lot_rec.serial_transaction_temp_id  := l_transaction_id  ;
DBMS_OUTPUT.PUT_LINE('STEP5');
                    INSERT INTO  MTL_TRANSACTION_LOTS_INTERFACE VALUES r_mtl_lot_rec;
                    DBMS_OUTPUT.PUT_LINE('STEP6');
               EXCEPTION WHEN OTHERS THEN
                   x_return_status := fnd_api.g_ret_sts_error;
                   x_msg_count := x_msg_count+1;
                   x_msg_data := 'Error While Inserting data to MTL_TRANSACTION_LOTS_INTERFACE :'||SQLERRM;
                   DBMS_OUTPUT.PUT_LINE('STEP7');
               END;
            END IF;
DBMS_OUTPUT.PUT_LINE('STEP8');
            COMMIT;
            --call Inventory open transaction api to perform transfer of material from source locator to destination locator.
            DBMS_OUTPUT.PUT_LINE('STEP9');
            x_return_status := null;
            x_msg_count := 0;
            X_MSG_DATA := null;
            dbms_output.put_line('Before Calling process_transactions'||l_transaction_id);
            lc_result := inv_txn_manager_pub.process_transactions
                                                 (p_api_version           => 1.0,
                                                  p_init_msg_list         => fnd_api.g_false,
                                                  p_commit                => fnd_api.g_true,
                                                  p_validation_level      => fnd_api.g_valid_level_full,
                                                  x_return_status         => x_return_status,
                                                  x_msg_count             => x_msg_count,
                                                  x_msg_data              => x_msg_data,
                                                  x_trans_count           => l_trans_count,
                                                  p_table                 => 1,
                                                  P_HEADER_ID             => L_TRANSACTION_ID
                                                 );
                                                 DBMS_OUTPUT.PUT_LINE('STEP10');
            dbms_output.put_line('Return Status'||x_return_status);
            IF NVL(x_return_status,'N') <> FND_API.g_ret_sts_success THEN
               x_return_status := FND_API.g_ret_sts_error;
               dbms_output.put_line('Return Status: '||x_return_status);
               dbms_output.put_line('Message: '||x_msg_data);
               DBMS_OUTPUT.PUT_LINE('STEP11');
            END IF;
         END IF;
         COMMIT;
      EXCEPTION WHEN OTHERS THEN
                dbms_output.put_line('In Exception'||sqlerrm);
                fnd_msg_pub.count_and_get (p_count        => x_msg_count,
                                           p_data         => x_msg_data,
                                           p_encoded      =>
                                          fnd_api.g_false
                                          );
                                          DBMS_OUTPUT.PUT_LINE('STEP12');
                 ROLLBACK;



end PKMTL_TRANSACTION;

Note :In the above procedure change the l_segment values accordingly.

Step2 : Run the Below anonymous block and pass the parameters accordingly for "Miscellaneous issue"

declare
X_RETURN_STATUS     varchar2(1000);
X_MSG_COUNT         number;
X_MSG_DATA          VARCHAR2(1000);

BEGIN
  
    BEGIN
      PKMTL_TRANSACTION(287955, 207, 'Stores',NULL, NULL, 'Ea', -1, 'Miscellaneous issue',
                NULL, null, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA);
                
dbms_output.put_line('X_RETURN_STATUS->'||X_RETURN_STATUS||'    X_MSG_COUNT->'||X_MSG_COUNT||'    X_MSG_DATA->'||X_MSG_DATA);

    END;

Run the Below anonymous block and pass the parameters accordingly for "Miscellaneous receipt"

declare
X_RETURN_STATUS     varchar2(1000);
X_MSG_COUNT         number;
X_MSG_DATA          VARCHAR2(1000);

BEGIN
  
    BEGIN
      PKMTL_TRANSACTION(287955, 207, 'Stores',NULL, NULL, 'Ea', 1, 'Miscellaneous receipt',
                NULL, null, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA);
                
dbms_output.put_line('X_RETURN_STATUS->'||X_RETURN_STATUS||'    X_MSG_COUNT->'||X_MSG_COUNT||'    X_MSG_DATA->'||X_MSG_DATA);

    END;