Chandra Sekhar Kadali - Oracle Apps Knowledge Sharing: SQL / PL/SQL

Showing posts with label SQL / PL/SQL. Show all posts

Tuesday, November 25, 2014

PL/SQL Developer Database Connection Issue

Right after you install PL/SQL Developer, you realize that you cannot connect to your databases. You get the following message:

ORA-12154: TNS:could not resolve the connect identifier specified

For a moment, you may think, it is something to do with TNSNAMES.ora file or Pinging issue. It is, however, purely a PL/SQL Developer issue. When I downloaded SQL Developer from Oracle and installed it, I could connect to all my databases fine.

The solution is actually pretty simple. There are probably multiple ORACLE_HOME defined (such as for reports and forms, workflow, discoverer, etc.) in your machine, and PL/SQL Developer is probably not finding the correct one to choose from. All you have to do is to choose the correct version for your PL/SQL Developer. To do so, follow the below instruction.

Make sure you are connected to Network/VPN.
Open PL/SQL Developer.
Click on Tools -> Preferences. Select the very first option: Connection.
From Oracle Home drop down list, select the correct version. For example, OH159997875 works for my machine.
Restart PL/SQL Developer

Change Oracle Prompt at SQL*PLUS

PRE10G:
-----------------

Enter following commands in glogin.sql file located at $ORACLE_HOME/sqlplus/admin directory or execute them one by one at SQL Prompt.

col username new_value username
col dbname new_value dbname
set termout off
SELECT lower(user) username,
       substr(global_name, 1, instr(global_name, '.')-1) dbname
FROM   global_name
/
set termout on
set sqlprompt '&&username@&&dbname> '

POST 10G:

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

Below is an example to change the SQL*Plus prompt (10g and up), simple and yet very useful.
SET SQLPROMPT command is used to change the default SQL> prompt

1) Display username

set sqlprompt '_user>' 2) Display database name
set sqlprompt '_connect_identifier_privilege>'
Step 2 and 3 can be combined together to display username and database name together.
3) Display username and database name (e.g. apps@dbname> )
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE>" 4) To set the time at sqlprompt
set time on
Now the best part is to avoid typing this command everytime you open a new SQL*Plus session, edit glogin.sql file located at $ORACLE_HOME/sqlplus/admin directory as follows.
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE>" set time on

Disable Interactive prompts at SQL*PLUS

Whenever we have ampersand "&" in our script, SQL Plus prompts for a value to be entered. There are several ways to avoid that prompt as discussed below

1) user chr(38) instead of &

SELECT 'I love SQL '||chr(38)||' PLSQL' from dual;

2) Use & at the end just below the single quotes

SELECT 'I love SQL &'||' PLSQL' from dual;

3) SET DEFINE OFF can be use to disable the prompt

SET DEFINE OFF
SELECT 'I love SQL & PLSQL ' from dual; 
SET DEFINE ON

Recompile inavlid objects using UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below:

PROCEDURE RECOMP_SERIAL(
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);

PROCEDURE RECOMP_PARALLEL(
threads IN PLS_INTEGER DEFAULT NULL,
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);

-- Schema level.
EXEC UTL_RECOMP.recomp_serial('APPS');
EXEC UTL_RECOMP.recomp_parallel(4, 'APPS');

-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'APPS');

Create form with cancel query option

Set the form level property Interaction Mode to Blocking or non-Blocking.
When set to non-Blocking, a dialog box appears that contains the following prompt:

Press cancel to end this database operation

When a long running query is executed in forms, this enables the option to interrupt the query by pressing the cancel button. However, this only works for Forms blocks which are based on standard tables or views.
It does not work for blocks based on stored procedures, where the data is returned by a ref cursor or a table of records.

Sometimes when we query form with higher data results the forms hangs and leaves us with option to wait until data is displayed or close it forcefully.
Using profile option FND: Enable Cancel Query, the cancel button with message "Press cancel to end this database operation" can be displayed that allows canceling query. The profile option may not support all forms but still you can have this option for most of the oracle provided forms.

Use of variable with comma seperated value in SQL Query

The requirement is to pass comma seperated value to a procedure and to use that variable in the query to extract values. For e.g. variable p_ord_num_list has a value of '90001234, 90001235, 90001236' and we attempt to use this in variable in the query as below

SELECT * FROM oe_order_headers_all WHERE order_number IN p_ord_num_list

The above query completes in error

ORA-01722: invalid number
ORA-06512: at line 13

The above requirement can be achieved in following way

DECLARE
   p_ord_num_list   VARCHAR2 (4000) := '90001234, 90001235, 90001236';
BEGIN
   FOR i IN (SELECT order_number
             FROM   oe_order_headers_all e
             WHERE  order_number IN (
                       SELECT EXTRACTVALUE (xt.COLUMN_VALUE, 'e')
                       FROM   TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE (   ''
                                                                    || REPLACE (p_ord_num_list, ',', '')
                                                                    || ''
                                                                   )
                                                         , '/ord_num/*'
                                                          )
                                                 )
                                    ) xt))
   LOOP
      DBMS_OUTPUT.put_line ('a = ' || i.order_number);
   END LOOP;
END;

The way above statement works is that it first generates the XML tag for each comma seperated value and then extracts values from each element.

The other way to do this is by using regular expression functions as shown below

DECLARE
   p_ord_num_list   VARCHAR2 (4000) := '90001234, 90001235, 90001236';
BEGIN
   FOR i IN (SELECT * FROM oe_order_headers_all WHERE order_number IN (         
SELECT     TRIM(REGEXP_SUBSTR(p_ord_num_list   , '[^,]+', 1, LEVEL)) item_id
FROM       (SELECT p_ord_num_list    str
            FROM   DUAL)
CONNECT BY LEVEL <= LENGTH(str) - LENGTH(REPLACE(str, ',')) + 1))
   LOOP
      DBMS_OUTPUT.put_line ('a = ' || i.order_number);
   END LOOP;
END;

Steps to resolve BI/XML Publisher: Leading and Trailing zeroes truncated for EXCEL Reports

Microsoft Excel is too smart and it identifies whether the value in the cell is a Text or number and applies formatting accordingly. This sometimes becomes an issue for us when we are trying to generate an excel report. For example item number 0003463262360 has all the numbers and starts with zero, this when printed in excel report displays it as 3463262360. Hence all the leading zeroes are truncated. Same issue happens when we have decimal and trailing zeroes.

FO formatting options can be used to get away with this problem. Below is the syntax for same.

Script to Create Credit Memo and apply it to an invoice

SET SERVEROUTPUT ON;

DECLARE
   -- This script was tested in 11i instance --
   v_return_status          VARCHAR2(1);
   p_count                  NUMBER;
   v_msg_count              NUMBER;
   v_msg_data               VARCHAR2(2000);
   v_request_id             NUMBER;
   v_context                VARCHAR2(2);
   l_cm_lines_tbl           arw_cmreq_cover.cm_line_tbl_type_cover;
   l_customer_trx_id        NUMBER;
   cm_trx_id                NUMBER;
   v_interface_header_rec   arw_cmreq_cover.pq_interface_rec_type;
   ind                      NUMBER;
   l_trx_number             VARCHAR2(30);

   CURSOR c_inv(p_trx_number VARCHAR2)
   IS
      SELECT rct.trx_number
           ,  rct.customer_trx_id
           ,  rctl.customer_trx_line_id
           ,  rctl.quantity_invoiced
           ,  unit_selling_price
      FROM   ra_customer_trx_all rct, ra_customer_trX_lines_all rctl
      WHERE  rct.customer_trx_id = rctl.customer_trx_id
      AND    trx_number = p_trx_number
      AND    line_type = 'LINE';

   PROCEDURE set_context
   IS
   BEGIN
      DBMS_APPLICATION_INFO.set_client_info(0);
      MO_GLOBAL.SET_POLICY_CONTEXT('S', 0);
   END set_context;
BEGIN
   -- Setting the context ----
   set_context;

   DBMS_OUTPUT.put_line('Invoking Credit Memo Creation process');

   l_trx_number := '20116773';

   FOR lc_inv IN c_inv(l_trx_number)
   LOOP
      ind := 1;
      l_customer_trx_id := lc_inv.customer_trx_id;

      l_cm_lines_tbl(ind).customer_trx_line_id := lc_inv.customer_trx_line_id;
      l_cm_lines_tbl(ind).quantity_credited := lc_inv.quantity_invoiced * -1;
      l_cm_lines_tbl(ind).price := lc_inv.unit_selling_price;
      l_cm_lines_tbl(ind).extended_amount := lc_inv.quantity_invoiced * lc_inv.unit_selling_price * -1;
   END LOOP;

   ar_credit_memo_api_pub.create_request( -- standard api parameters
                                         p_api_version                  => 1.0
                                       ,  p_init_msg_list                => fnd_api.g_true
                                       ,  p_commit                       => fnd_api.g_false
                                       -- credit memo request parameters
                                       ,  p_customer_trx_id                => l_customer_trX_id
                                       ,  p_line_credit_flag             => 'Y'
                                       ,  P_CM_LINE_TBL                  => l_cm_lines_tbl
                                       ,  p_cm_reason_code               => 'RETURN'
                                       ,  p_skip_workflow_flag           => 'Y'
                                       ,  p_batch_source_name            => 'XX_ORDER_ENTRY'
                                       ,  p_interface_attribute_rec      => v_interface_header_rec
                                       ,  p_credit_method_installments   => NULL
                                       ,  p_credit_method_rules          => NULL
                                       ,  x_return_status                => v_return_status
                                       ,  x_msg_count                    => v_msg_count
                                       ,  x_msg_data                     => v_msg_data
                                       ,  x_request_id                   => v_request_id);
   DBMS_OUTPUT.put_line('Message count ' || v_msg_count);

   IF v_msg_count = 1
   THEN
      DBMS_OUTPUT.put_line('l_msg_data ' || v_msg_data);
   ELSIF v_msg_count > 1
   THEN
      LOOP
         p_count := p_count + 1;
         v_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);

         IF v_msg_data IS NULL
         THEN
            EXIT;
         END IF;

         DBMS_OUTPUT.put_line('Message' || p_count || ' ---' || v_msg_data);
      END LOOP;
   END IF;

   IF v_return_status <> 'S'
   THEN
      DBMS_OUTPUT.put_line('Failed');
   ELSE
      SELECT cm_customer_trx_id
      INTO   cm_trx_id
      FROM   ra_cm_requests_all
      WHERE  request_id = v_request_id;

      DBMS_OUTPUT.put_line(' CM trx_id = ' || cm_trx_id);
   -- You can issue a COMMIT; at this point if you want to save the created credit memo to the database
   -- COMMIT;
   END IF;
END;

BOM Details Query

SELECT DISTINCT LPAD (' ', LEVEL * 2) || LEVEL order_level
              , msib.segment1 assembly_item
              , msib.description assembly_description
              , msib.inventory_item_status_code assembly_item_status
              , SYS_CONNECT_BY_PATH (msib2.segment1, '/') PATH
              , msib2.segment1 AS component_item
              , msib2.description component_item_description
              , msib2.inventory_item_status_code component_item_status
              , bic.item_num
              , bic.operation_seq_num
              , bic.component_quantity
FROM            bom.bom_components_b bic
              , bom.bom_structures_b bom
              , inv.mtl_system_items_b msib
              , inv.mtl_system_items_b msib2
              , mtl_parameters mp
WHERE           1 = 1
AND             bic.bill_sequence_id = bom.bill_sequence_id
AND             SYSDATE BETWEEN bic.effectivity_date AND Nvl(bic.disable_date, SYSDATE)
AND             bom.assembly_item_id = msib.inventory_item_id
AND             bom.organization_id = msib.organization_id
AND             bic.component_item_id = msib2.inventory_item_id
AND             bom.organization_id = msib2.organization_id
AND             mp.organization_id = msib.organization_id
AND             mp.organization_code = :p_org_code            /* organization here */
AND             bom.alternate_bom_designator IS NULL
START WITH      msib.segment1 = :p_item_number                /*  component item to be used here */
CONNECT BY NOCYCLE PRIOR bic.component_item_id = msib.inventory_item_id
ORDER BY        PATH

Accounting Entries in Order to Cash Cycle

Sales order creation – No entries
    Pick release:
    Inventory Stage A/c…………………Debit
    Inventory Finished goods a/c……..Credit
    Ship confirm:
    Cogs A/c ……………………………Debit
    Inventory Organization a/c………Credit
    Receviable:
    Receviable A/c………………………Debit
    Revenue A/c………………………Credit
    Tax ………………..…………………Credit
    Freight…………..….……………….Credit
    Cash:
    Cash A/c Dr…………………………Debit
    Receivable A/c……………………….Credit

Accounting Entries in Procure to Pay cycle

Purchase Requisition creation: No entry
    Purchase Order creation: No entry
    Inventory Receipt:
    Inventory A/c…………….Debit
    AP Accrual A/C………Credit(This A/c We are giving in Financial Option)
    At the time of Matching the Invoice with Purchase Order
    AP Accrual A/c………….Debit
    Supplier A/c…………..Credit
    At the time of making payment to supplier
    Supplier A/C…………… Debit
    Bank A/c…………….Credit

Query to display BOL, MBOL, Trip details for a Sales Order

SELECT   ooh.order_number
       , wnd.NAME delivery_name
       , wt.NAME trip_name
       , ool.line_number
       , ool.ordered_item
       , ool.flow_status_code
       , DECODE (wdd.released_status
               , 'R', 'Ready For Release'
               , 'B', 'Back Ordered'
               , 'S', 'Released To Warehouse'
               , 'D', 'Cancelled'
               , 'N', 'Not Ready For Release'
               , 'Y', 'Staged or Pick Confirmed'
               , 'C', 'Interfaced/Shipped'
               , 'I', 'Interfaced/Shipped'
               , 'O', 'Not Shipped'
                ) delivery_status
       , rct.trx_number invoice_number
       , wdd.released_status
       , ood.organization_name || ' (' || ood.organization_code || ')' ship_from_org
       , hp_carrier.party_name carrier_name
       , wdi.sequence_number bol_number
       , wds.departure_net_weight ship_weight
       , wds.actual_departure_date ship_date
       , ooh.cust_po_number
       , SUBSTR (hp.party_name, 1, 30) || ' ' || 
       SUBSTR (hl_ship.address1, 1, 36) || ' ' || 
       SUBSTR (hl_ship.address2, 1, 36) || ' ' || 
       SUBSTR (hl_ship.city, 1, 30) || ' ' || 
       SUBSTR (hl_ship.province , 1 , 2 ) || ' ' || 
       SUBSTR (hl_ship.postal_code , 1 , 8 ) ship_to_address
FROM     apps.hr_locations hl
       , org_organization_definitions ood
       , apps.oe_order_headers_all ooh
       , oe_order_lines_all ool
       , apps.hz_locations hl_ship
       , apps.hz_parties hp
       , apps.hz_party_sites hps
       , apps.hz_cust_acct_sites_all hcas
       , apps.hz_cust_site_uses_all hcsu
       , apps.hz_party_sites hps_bill
       , apps.hz_cust_acct_sites_all hcas_bill
       , apps.hz_cust_site_uses_all hcsu_bill
       , apps.wsh_delivery_details wdd
       , apps.wsh_new_deliveries wnd
       , apps.wsh_delivery_assignments wda
       , apps.wsh_trips wt
       , apps.wsh_delivery_legs wdl
       , apps.wsh_trip_stops wds
       , apps.wsh_document_instances wdi
       , apps.hz_parties hp_carrier
       , ra_customer_trx_all rct
WHERE    1 = 1
AND      ooh.header_id = ool.header_id
AND      ood.organization_id = ool.ship_from_org_id
AND      ooh.ship_from_org_id = hl.inventory_organization_id
AND      hl_ship.location_id = hps.location_id
AND      hp.party_id = hps.party_id
AND      hps.party_site_id = hcas.party_site_id
AND      hps_bill.party_site_id = hcas_bill.party_site_id
AND      hps_bill.party_id = hp.party_id
AND      hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND      hcsu.site_use_id = ooh.ship_to_org_id
AND      hcas_bill.cust_acct_site_id = hcsu_bill.cust_acct_site_id
AND      hcsu_bill.site_use_id = ooh.invoice_to_org_id
AND      ooh.header_id = wdd.source_header_id(+)
AND      wda.delivery_detail_id(+) = wdd.delivery_detail_id
AND      wda.delivery_id = wnd.delivery_id(+)
AND      ool.line_id = wdd.source_line_id
AND      wt.trip_id(+) = wds.trip_id
AND      wds.stop_id(+) = wdl.pick_up_stop_id
AND      wdl.delivery_id(+) = wnd.delivery_id
AND      hp_carrier.party_id(+) = wt.carrier_id
AND      wdi.entity_id(+) = wdl.delivery_leg_id
AND      wdi.entity_name(+) = 'WSH_DELIVERY_LEGS'
AND      TO_CHAR (ooh.order_number) = rct.ct_reference(+)
AND      TO_CHAR (ooh.order_number) = rct.interface_header_attribute1(+)
AND      interface_header_context(+) = 'ORDER ENTRY'
AND      ooh.order_number = :order_number
ORDER BY ool.flow_status_code
       , ooh.order_number
       , ool.line_number

Query to list active serial number for an item

ELECT msi.segment1 item_number
     , msn.serial_number
     , msn.current_status_name
     , msn.status_code
     , msn.current_subinventory_code
     , ml.concatenated_segments
FROM   mtl_serial_numbers_all_v msn
     , mtl_system_items_b msi
     , mtl_item_locations_kfv ml
     , mtl_parameters mp
WHERE  msi.inventory_item_id = msn.inventory_item_id
AND    mp.organization_code = msn.organization_code
AND    ml.inventory_location_id = msn.current_locator_id
AND    msn.current_status = 3
AND    mp.organization_id = msi.organization_id
AND    mp.organization_code =  :org_code
AND    msi.segment1 = :item

Relation between AR invoice and OM

Many times I have seen a question being asked in the forums about the relationship between AR Invoice and Sales Order.
There are several interface_line_attribute and interface_header_attribute columns in RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL table respectively which are used to map it with other modules.
The relationship is actually mapped using the descriptive flexfield.

Query for following
Application: Receivables
Title: Line Transaction Flexfield

and then in the context field is the listed different modules.

For Sales order search for Order Management or Order Entry and click on segments to see how they are mapped. Screenshots below

Click on the segments button to see column mappings.

The above method can also be used to find mapping of Receivables with other modules like Oracle Projects, Services, Contracts etc.

Package realted to Permit Sales by Item

create or replace PACKAGE        xxar_extract_permit_sales_pkg AUTHID CURRENT_USER IS
/*****************************************************************************/
/*   Name: xxar_extract_permit_sales_pkg                                     */
/*   Goal: Package contains functions and variables used for                 */
/*         report extract_permit_sales                                       */
/*   Parameters          : NA                                               */
/*                                                                           */
/*   Created by        Date        Description                               */
/*   ----------------- ---------- -----------------------------------       */
/*   Chandra Kadali    12/11/2014 Initial coding                            */
/*                                                                           */
/*   History of modification                                                 */
/*   Name                   Date        DDM Description                     */
/*   -------------------- ---------- ---- --------------------------------*/
/*****************************************************************************/

p_order_type        VARCHAR2(1000);
p_from_date         DATE;
p_to_date           DATE;
p_customer                   VARCHAR2(1000);
p_permit_class           VARCHAR2(1000);
p_permit_number VARCHAR2(100);

FUNCTION get_shipment_date(p_line_id IN NUMBER) RETURN VARCHAR2;
FUNCTION get_cdc_code(p_item_id IN Number,p_org_id IN NUMBER) RETURN VARCHAR2 ;
   FUNCTION get_cdc_code_desc(p_item_id IN Number,p_org_id IN NUMBER) RETURN VARCHAR2 ;
FUNCTION get_rma_code(p_item_id IN NUMBER,p_org_id IN NUMBER) RETURN VARCHAR2;
FUNCTION get_rma_code_desc(p_item_id IN NUMBER,p_org_id IN NUMBER) RETURN VARCHAR2;
FUNCTION get_milk_class(p_item_id IN NUMBER,p_org_id IN NUMBER) RETURN VARCHAR2;
FUNCTION get_error_desc(p_item_id IN NUMBER,p_org_id IN NUMBER) RETURN VARCHAR2;
FUNCTION get_lot_details(p_item_id IN NUMBER,p_org_id IN NUMBER) RETURN VARCHAR2;

END xxar_extract_permit_sales_pkg;

create or replace PACKAGE BODY xxar_extract_permit_sales_pkg IS
/*****************************************************************************/
/*   Name: xxont_cust_contact_pkg                                       */
/*   Goal: Package contains functions used for                 */
/*         report control_permit_sales                                      */
/*   Parameters          : NA                                               */
/*                                                                           */
/*   Created by        Date        Description                               */
/*   ----------------- ---------- -----------------------------------       */
/*   Chandra Kadali    12/11/2014 Initial coding                            */
/*                                                                           */
/*   History of modification                                                 */
/*   Name                   Date        DDM Description                     */
/*   -------------------- ---------- ---- --------------------------------*/
/*****************************************************************************/

   /*****************************************************************************/
/* Name of the function : get_shipment_date                                     */
/* logic : Function to derive the Address                            */
/* Creation:                                                                 */
/* Name : Chandra Kadali    Date : 12/11/2014                                 */
/*                                                                           */
/* History of modification                                                   */
/* Name                  Date         Change Description                    */
/* -------------------- ---------- ---- ----------------------------------*/
/*****************************************************************************/

FUNCTION get_shipment_date(p_line_id IN NUMBER) RETURN VARCHAR2 IS
    v_shipment_date                VARCHAR2(200) := NULL;

    BEGIN

   SELECT TO_CHAR(orl.actual_shipment_date,
                   'YYYY/MM/DD') shipment_date
           INTO v_shipment_date

           FROM oe_order_lines orl
           WHERE line_id = p_line_id;


     RETURN v_shipment_date;

EXCEPTION
    WHEN OTHERS THEN
      v_shipment_date := NULL;
      RETURN v_shipment_date;
END get_shipment_date;

   /*****************************************************************************/
/* Name of the function : get_cdc_code                                     */
/* logic :                             */
/* Creation:                                                                 */
/* Name : Chandra Kadali    Date : 12/11/2014                                 */
/*                                                                           */
/* History of modification                                                   */
/* Name                  Date         Change Description                    */
/* -------------------- ---------- ---- ----------------------------------*/
/*****************************************************************************/

FUNCTION get_cdc_code(p_item_id IN NUMBER,
                        p_org_id IN NUMBER) RETURN VARCHAR2 IS

    v_cdc_code VARCHAR2(200) := NULL;

BEGIN

    SELECT category_concat_segs

      INTO v_cdc_code
      FROM mtl_item_categories_v
     WHERE inventory_item_id = p_item_id
       AND organization_id = p_org_id
       AND category_set_name = 'AGR CODE CDC';

    RETURN v_cdc_code;

EXCEPTION
    WHEN OTHERS THEN
      v_cdc_code := NULL;
      RETURN v_cdc_code;
END get_cdc_code;

/*****************************************************************************/
/* Name of the function : get_cdc_code_desc                                     */
/* logic :                             */
/* Creation:                                                                 */
/* Name : Chandra Kadali    Date : 12/11/2014                                 */
/*                                                                           */
/* History of modification                                                   */
/* Name                  Date         Change Description                    */
/* -------------------- ---------- ---- ----------------------------------*/
/*****************************************************************************/

FUNCTION get_cdc_code_desc(p_item_id IN NUMBER,
                        p_org_id IN NUMBER) RETURN VARCHAR2 IS

    v_cdc_code_desc VARCHAR2(200) := NULL;
    v_cdc_code varchar2(200):=null;
    v_category_id number:=null;
    v_category_set_id number:=null;

BEGIN
begin
    SELECT category_concat_segs
    ,mtl_item_categories_v.CATEGORY_SET_ID
    ,category_id
      INTO v_cdc_code
      ,v_category_set_id
      ,v_category_id
      FROM mtl_item_categories_v
     WHERE inventory_item_id = p_item_id
       AND organization_id = p_org_id
       AND category_set_name = 'AGR CODE CDC';

    --RETURN v_cdc_code;

EXCEPTION
    WHEN OTHERS THEN
      v_cdc_code := NULL;
      v_category_set_id:=null;
      v_category_id:=null;
     -- RETURN v_cdc_code;
--END get_cdc_code;
end;

if(v_cdc_code is not null) then
begin
select description into v_cdc_code_desc from mtl_categories_vl
where category_id=v_category_id
--and language=USERENV('LANG')
;
return v_cdc_code_desc;
exception when others then
v_cdc_code_desc:=null;
return v_cdc_code_desc;
end;
else
v_cdc_code_desc:=null;
return v_cdc_code_desc;
end if;
END get_cdc_code_desc;

   /*****************************************************************************/
/* Name of the function : get_rma_code                                     */
/* logic :                             */
/* Creation:                                                                 */
/* Name : Chandra Kadali    Date : 12/11/2014                                 */
/*                                                                           */
/* History of modification                                                   */
/* Name                  Date         Change Description                    */
/* -------------------- ---------- ---- ----------------------------------*/
/*****************************************************************************/

FUNCTION get_rma_code(p_item_id IN NUMBER,
                        p_org_id IN NUMBER) RETURN VARCHAR2 IS

    v_rma_code VARCHAR2(200) := NULL;

BEGIN

    SELECT category_concat_segs
      INTO v_rma_code
      FROM mtl_item_categories_v
     WHERE inventory_item_id = p_item_id
       AND organization_id = p_org_id
       AND category_set_name = 'AGR RMA GROUP';

    RETURN v_rma_code;

EXCEPTION
    WHEN OTHERS THEN
      v_rma_code := NULL;
      RETURN v_rma_code;
END get_rma_code;

   /*****************************************************************************/
/* Name of the function : get_rma_code_desc                                     */
/* logic :                             */
/* Creation:                                                                 */
/* Name : Chandra Kadali    Date : 12/11/2014                                 */
/*                                                                           */
/* History of modification                                                   */
/* Name                  Date         Change Description                    */
/* -------------------- ---------- ---- ----------------------------------*/
/*****************************************************************************/

FUNCTION get_rma_code_desc(p_item_id IN NUMBER,
                        p_org_id IN NUMBER) RETURN VARCHAR2 IS

    v_rma_code VARCHAR2(200) := NULL;
    v_rma_code_desc VARCHAR2(200) := NULL;
    v_category_id number;

BEGIN
begin
    SELECT category_concat_segs,category_id
      INTO v_rma_code,v_category_id
      FROM mtl_item_categories_v
     WHERE inventory_item_id = p_item_id
       AND organization_id = p_org_id
       AND category_set_name = 'AGR RMA GROUP';

    ---RETURN v_rma_code;

EXCEPTION
    WHEN OTHERS THEN
      v_rma_code := NULL;
      --RETURN v_rma_code;
      end;

      if(v_rma_code is not null ) then
      begin
      select description into v_rma_code_desc from mtl_categories_vl where category_id=v_category_id ;--and language=USERENV('LANG');
      return v_rma_code_desc;
      exception when others then
      v_rma_code_desc:=null;
      return v_rma_code_desc;
      end;
      end if;
      return v_rma_code_desc;
END get_rma_code_desc;

   /*****************************************************************************/
/* Name of the function : get_milk_class                                     */
/* logic :                             */
/* Creation:                                                                 */
/* Name : Chandra Kadali    Date : 12/11/2014                                 */
/*                                                                           */
/* History of modification                                                   */
/* Name                  Date         Change Description                    */
/* -------------------- ---------- ---- ----------------------------------*/
/*****************************************************************************/

FUNCTION get_milk_class(p_item_id IN NUMBER,
                        p_org_id IN NUMBER) RETURN VARCHAR2 IS

    v_rma_code VARCHAR2(200) := NULL;
    v_milk_class VARCHAR2(200) := NULL;

BEGIN

    SELECT category_concat_segs
      INTO v_rma_code
      FROM mtl_item_categories_v
     WHERE inventory_item_id = p_item_id
       AND organization_id = p_org_id
       AND category_set_name = 'AGR RMA GROUP';

       select attribute1 into v_milk_class from mtl_categories where
        segment1 = v_rma_code
       and attribute_category='AGR RMA GROUP CATEGORY';

    RETURN v_milk_class;

EXCEPTION
    WHEN OTHERS THEN
      v_milk_class := NULL;
      RETURN v_milk_class;
END get_milk_class;

   /*****************************************************************************/
/* Name of the function : get_error_desc                                     */
/* logic :                             */
/* Creation:                                                                 */
/* Name : Chandra Kadali    Date : 12/11/2014                                 */
/*                                                                           */
/* History of modification                                                   */
/* Name                  Date         Change Description                    */
/* -------------------- ---------- ---- ----------------------------------*/
/*****************************************************************************/

FUNCTION get_error_desc(p_item_id IN NUMBER,
                        p_org_id IN NUMBER) RETURN VARCHAR2 IS

    v_error varchar2(4000) :=NULL;


BEGIN

    SELECT decode(xxar_extract_permit_sales_pkg.get_cdc_code(p_item_id,p_org_id),NULL,'Missing CDC||','') ||
           decode(xxar_extract_permit_sales_pkg.get_rma_code(p_item_id,p_org_id),NULL,'Missing RMA||','') ||
           decode(xxar_extract_permit_sales_pkg.get_milk_class(p_item_id,p_org_id),NULL,'Missing Milk Class','')

           INTO v_error

           FROM DUAL;


    RETURN v_error;

EXCEPTION
    WHEN OTHERS THEN
      v_error := NULL;
      RETURN v_error;
END get_error_desc;

FUNCTION get_lot_details(p_item_id IN NUMBER,
                        p_org_id IN NUMBER) RETURN VARCHAR2 IS

cursor c_lot_number is
SELECT lot_number from mtl_lot_numbers where inventory_item_id=p_item_id and organization_id=p_org_id;
    v_lot varchar2(4000) :=NULL;


BEGIN
for rec_lot_number in c_lot_number
loop

v_lot:=v_lot||rec_lot_number.lot_number|| '     ';
end loop;


    RETURN v_lot;


EXCEPTION
    WHEN OTHERS THEN
      v_lot := NULL;
      RETURN v_lot;
END get_lot_details ;

END xxar_extract_permit_sales_pkg;

Query to get Permit Sales by Item

SELECT DISTINCT
obh.attribute2 PERMIT_CLASS,
hca.account_number,
hca.account_name,
msib.segment1 ITEM_CODE,
msib.description ITEM_DESC,
-- CDC
xxar_extract_permit_sales_pkg.get_cdc_code(orl.inventory_item_id,orl.ship_from_org_id) CDC,
xxar_extract_permit_sales_pkg.get_cdc_code_desc(orl.inventory_item_id,orl.ship_from_org_id) CDC_DESC,
--RMA GROUP
xxar_extract_permit_sales_pkg.get_rma_code(orl.inventory_item_id,orl.ship_from_org_id) RMA,
xxar_extract_permit_sales_pkg.get_rma_code_desc(orl.inventory_item_id,orl.ship_from_org_id) RMA_DESC,
--MILK CLASS based on RMA GROUP
xxar_extract_permit_sales_pkg.get_milk_class(orl.inventory_item_id,orl.ship_from_org_id) MILK_CLASS,
obh.cust_po_number PERMIT,
rct.trx_number TRX_NUMBER,
rctype.name TRX_TYPE,
ooh.order_number,
--- Actual shipment date for Order or Return as per FS for return the shipment date will be the one in the original transaction referenced in the RMA transaction
xxar_extract_permit_sales_pkg.get_shipment_date(DECODE(ooh.order_category_code,'ORDER',orl.line_id,'RETURN',orl.reference_line_id))SHIPMENT_DATE,
rct.trx_date,
rctgl.gl_date gl_date,
orl.ordered_quantity ,
orl.unit_selling_price,
(orl.ordered_quantity*orl.unit_selling_price) amount,
xxar_extract_permit_sales_pkg.get_lot_details(orl.inventory_item_id,orl.ship_from_org_id) lot_number
FROM ra_customer_trx rct,
oe_order_headers ooh,
oe_order_lines orl,
hz_cust_accounts hca,
mtl_system_items_b msib,
ra_cust_trx_types rctype,
oe_blanket_headers obh ,
oe_blanket_lines obl ,
oe_transaction_types_vl ottv,
ra_cust_trx_line_gl_dist rctgl,
ra_batch_sources rbatchs,
RA_CUSTOMER_TRX_LINES_ALL rl
WHERE to_char(ooh.order_number)     = (rct.interface_header_attribute1)--ct_reference)
AND ooh.header_id            = orl.header_id
and rct.interface_header_context = 'ORDER ENTRY'
and rct.org_id=121
and rl.line_type = 'LINE'
and rl.interface_line_context = 'ORDER ENTRY'
and rctgl.customer_trx_line_id=rl.customer_trx_line_id
and rl.interface_line_attribute6 = to_char(orl.line_id)
and rl.interface_line_attribute1 = to_char(ooh.order_number)
and rl.sales_order = ooh.order_number
AND rct.sold_to_customer_id = hca.cust_account_id
AND rl.inventory_item_id(+) = msib.inventory_item_id
AND msib.inventory_item_id   = orl.inventory_item_id
AND msib.organization_id     = orl.ship_from_org_id
AND rctype.cust_trx_type_id = rct.cust_trx_type_id
AND rct.sold_to_customer_id =obl.sold_to_org_id
AND obh.header_id            =obl.header_id
AND ottv.transaction_type_id = obh.order_type_id
AND ooh.order_category_code = DECODE(:p_order_type,'STANDARD','ORDER','RMA','RETURN','ALL',ooh.order_category_code) --:p_order_type for standard or RMA
AND TRUNC(rctgl.gl_date) BETWEEN :p_from_date AND :p_to_date
AND NVL(hca.account_number,-1) = NVL(:p_customer,NVL(hca.account_number,-1))
AND NVL(obh.attribute2,    -1) = NVL(:p_permit_class,NVL(obh.attribute2,-1))
AND NVL(obh.cust_po_number,-1) = NVL(:p_permit_number,NVL(obh.cust_po_number,-1))
AND rctgl.customer_trx_id   =rct.customer_trx_id
AND rct.batch_source_id     =rbatchs.batch_source_id
AND ottv.name               =ANY('CDC PERMIT (CCBU_CA)','PERMIS CCL (CCBU_CA)')
AND upper(rctype.name)      =ANY('INVOICE','DEBIT','CREDIT')
AND ooh.order_category_code = ANY('ORDER','RETURN')
AND rbatchs.name            ='ORDER MANAGEMENT'
--Parameter selection Criteria
AND ooh.order_category_code = DECODE(:p_order_type,'STANDARD','ORDER','RMA','RETURN','ALL',ooh.order_category_code) --:p_order_type for standard or RMA
AND TRUNC(rctgl.gl_date) BETWEEN :p_from_date AND :p_to_date
AND NVL(hca.account_number,-1) = NVL(:p_customer,NVL(hca.account_number,-1))
AND NVL(obh.attribute2,    -1) = NVL(:p_permit_class,NVL(obh.attribute2,-1))
AND NVL(obh.cust_po_number,-1) = NVL(:p_permit_number,NVL(obh.cust_po_number,-1))
group by
obh.attribute2,
hca.account_number,
hca.account_name,
msib.segment1 ,
msib.description,
-- CDC
xxar_extract_permit_sales_pkg.get_cdc_code(orl.inventory_item_id,orl.ship_from_org_id) ,
xxar_extract_permit_sales_pkg.get_cdc_code_desc(orl.inventory_item_id,orl.ship_from_org_id),
--RMA GROUP
xxar_extract_permit_sales_pkg.get_rma_code(orl.inventory_item_id,orl.ship_from_org_id) ,
xxar_extract_permit_sales_pkg.get_rma_code_desc(orl.inventory_item_id,orl.ship_from_org_id),
--MILK CLASS based on RMA GROUP
xxar_extract_permit_sales_pkg.get_milk_class(orl.inventory_item_id,orl.ship_from_org_id) ,
obh.cust_po_number ,
rct.trx_number ,
rctype.name ,
ooh.order_number,
--- Actual shipment date for Order or Return as per FS for return the shipment date will be the one in the original transaction referenced in the RMA transaction
xxar_extract_permit_sales_pkg.get_shipment_date(DECODE(ooh.order_category_code,'ORDER',orl.line_id,'RETURN',orl.reference_line_id)),
rct.trx_date,
rctgl.gl_date,
orl.ordered_quantity ,
orl.unit_selling_price,
(orl.ordered_quantity*orl.unit_selling_price)
,   xxar_extract_permit_sales_pkg.get_lot_details(orl.inventory_item_id,orl.ship_from_org_id)
ORDER BY obh.attribute2,
hca.account_number,
hca.account_name,
msib.segment1 ,
msib.description,
-- CDC
xxar_extract_permit_sales_pkg.get_cdc_code(orl.inventory_item_id,orl.ship_from_org_id) ,
xxar_extract_permit_sales_pkg.get_cdc_code_desc(orl.inventory_item_id,orl.ship_from_org_id),
--RMA GROUP
xxar_extract_permit_sales_pkg.get_rma_code(orl.inventory_item_id,orl.ship_from_org_id) ,
xxar_extract_permit_sales_pkg.get_rma_code_desc(orl.inventory_item_id,orl.ship_from_org_id),
--MILK CLASS based on RMA GROUP
xxar_extract_permit_sales_pkg.get_milk_class(orl.inventory_item_id,orl.ship_from_org_id) ,
obh.cust_po_number ,
rct.trx_number ,
rctype.name ,
ooh.order_number,
--- Actual shipment date for Order or Return as per FS for return the shipment date will be the one in the original transaction referenced in the RMA transaction
xxar_extract_permit_sales_pkg.get_shipment_date(DECODE(ooh.order_category_code,'ORDER',orl.line_id,'RETURN',orl.reference_line_id)),
rct.trx_date,
rctgl.gl_date,
orl.ordered_quantity ,
orl.unit_selling_price,
(orl.ordered_quantity*orl.unit_selling_price),
   xxar_extract_permit_sales_pkg.get_lot_details(orl.inventory_item_id,orl.ship_from_org_id)

Thursday, October 09, 2014

Friday, September 26, 2014

Oralce ERS Invoice Processing

AIM

MD.050 Application Extensions Functional Design

Hudson’s Bay Company

Oracle ERS invoicing process

Author: Oleg Margolin

Creation Date: October 16, 2012

Last Updated: July 2, 2013

Document Ref:

Version: V1.0

Approvals:

Copy Number _____

Document Control

Change Record

Date	Author	Version	Change Reference
	Ol
October 16 2012	Oleg Margolin	Draft 1.0	No Previous Document
Apr 2013	Oleg Margolin		Move location of quantity and price fields to global_attributes

Reviewers

Name	Position

Janice Lusanne	Financial Systems Manager
Ghazi Omran

Distribution

Copy No.	Name	Location

1	Library Master	Project Library
2		Project Manager
3
4

Note To Holders:

If you receive an electronic copy of this document and print it out, please write your name on the equivalent of the cover page, for document control purposes.

If you receive a hard copy of this document, please write your name on the front cover, for document control purposes.

Contents

Document Control................................................................................................................................ ii

Topical Essay.......................................................................................................................................... 1

Basic Business Needs................................................................................................................... 1

Major Features................................................................................................................................ 4

User Procedures............................................................................................................................. 4

Business Rules............................................................................................................................... 4

Assumptions.................................................................................................................................. 4

Technical Overview.............................................................................................................................. 5

Form and Report Descriptions.......................................................................................................... 6

Form to move pending 3WAY receipts to AP (XXHBC_3WAY_ERS).......................... 6

Concurrent Program Description (HBC Process Receipts Package Name: XXHBC_3WAY_ALL_RECEIPT)..................................................................................................... 1

When to Run the Program.......................................................................................................... 1

Log Output...................................................................................................................................... 1

Restart Procedures........................................................................................................................ 1

Technical Overview...................................................................................................................... 1

Logic Summary.............................................................................................................................. 3

Payment remittance process ELECTRONIC (EDI820) – xxhbc_3way_edi820_pkg......... 7

Consignment invoice EDI820 mapping............................................................................... 17

When to Run the Program........................................................................................................ 20

Launch Parameters.................................................................................................................... 20

Restart Procedures...................................................................................................................... 21

Payment remittance process PAPER (EDI820) – xxhbc_edi820_remittance.................... 22

When to Run the Program.......................................................................................................... 1

Launch Parameters....................................................................................................................... 1

Restart Procedures........................................................................................................................ 1

Open and Closed Issues for this Deliverable................................................................................ 2

Open Issues..................................................................................................................................... 2

Closed Issues.................................................................................................................................. 2

Topical Essay

There is a need to move all merchandise invoicing from Retek (ERS) to Oracle ERP system. This will allow elimination of the ERS process from Retek. This change will also allow easier movement of invoices from 3way match process to ERS.

Basic Business Needs

All merchandise receipts will be interfaced to Oracle and the system will determine whether to pay the receipts through AP directly or, if the vendor is 3way match type of vendor, wait for the EDI810 invoice from the vendor. For these 3way vendors, the receipts will flow directly to the existing 3way match system.

Current Process

New Process

Major Features

i. Staging table of all receipts will be populated by Retek daily. These will be interfaced directly to AP or wait for an EDI810 invoice from the vendor. The split is based on vendors’ EDI810 effective date.

ii. Allow the user to move a receipt from 3way match process and pay it directly by AP.

iii. All EDI820 remittances will be generated from Oracle. The transactions will be at SKU level as required by vendors.

iv. Tax amounts on non-3way match invoices (ERS) will now be calculated in Oracle based on tax code and Province provided on Receipt interface. Oracle Tax rates will be used.

v. Will remove “Tester” skus from receipts passed from Retek into 3way match and ERS process.

User Procedures

The Oracle Concurrent manager process will run through ESP. This process will run daily to process the Receipt file. Receipts will be moved directly to AP or to 3way match process. This will be determined by the vendor’s EDI810 effective date.

User will have a screen to move receipts from 3way match to AP directly (as ERS) if required.

Business Rules

Assumptions

Technical Overview

A new table (XXHBC_RECEIPTS) will be created to hold ALL merchandise receipts. This table will be processed daily (HBC Process Receipts) to determine if these receipts are for 3-way match process or need to be directly paid (old ERS). For non-3way receipts, the invoices and off-po adjustments will be inserted to Oracle AP Open interface table.

EDI820 process will now include all AP invoices and details (when available) on the interface. The current 3way EDI820 process (HBC 3Way EDI820 Payment Outbound) will be modified to include all vendor payments and details.

Form and Report Descriptions

Form to move pending 3WAY receipts to AP (XXHBC_3WAY_ERS)

New form to move receipts from 3way pending receipts directly to AP for payment will be developed. Create a screen that allows user to query XXHBC_3WAY_PO_SHIP_IFACE table records. Only show shipments which have po_line_locations_all Quantity_invoiced = 0

· Allow user to query the table to find the required records to move to AP. Allow Query by Site number, ASN, PO number, Location. Any or all these fields can be queried on. Meaning user can enter any of the fields to search on and click ‘SEARCH’ button

· After SEARCH button is pressed, show ALL details for this selection to confirm the correct detail data is found. Note: Calculate Total column as Unit_price * Quantity Received. The Total Selected field is the total of all Total lines.

· User can then press the ‘PAY BY ERS’ button to move the shipment to pay through AP without waiting for EDI810 invoice.

· Ask user to confirm selection. Message:”(Are you sure you want to Move these records to ERS payment?”

· If user confirms, insert the selected details selected to XXHBC_RECEIPTS with STATUS = “ERS’. Keep the creation_date field same as on XXHBC_3WAY_PO_SHIP_IFACE table. PO_APPROVE_DATE can be sysdate.

· Delete these records from XXHBC_3WAY_PO_SHIP_IFACE, po_line_locations_all, po_line_distributions.

Concurrent Program Description (HBC Process Receipts Package Name: XXHBC_3WAY_ALL_RECEIPT)

The HBC Process Receipts concurrent program is needed process all receipts from Retek on a daily basis. The split will be based on the vendor’s EDI810 effective date and the PO approve date sent on the receipt interface table.

When to Run the Program

Program will run daily

Log Output

The log output consists of standard record counts and program execution statements.

Restart Procedures

Restart procedures for concurrent program are as follows:

· Rerun program.

Technical Overview

Retek system will populate the Oracle ERP custom table XXHBC_RECEIPTS daily. This table will be processed by the HBC Process Receipts request to ‘split’ the receipts into either 3way or non-3way receipts based on the PO Approve date (on receipt record) and the 3way Match Effective Date (on vendor site record). 3way receipts will be placed into XXHBC_3WAY_PO_SHIP_IFACE table for regular (existing) 3way processing. Non-3way receipts will be placed directly onto AP Open interface tables. Off-PO discounts will also be calculated and placed on the Open Interface tables. Taxes are to be calculated if required.

XXHBC_RECEIPTS table definition

CREATE TABLE XXHBC_RECEIPTS

(

PO_NUMBER VARCHAR2(20 BYTE) NOT NULL,

BUYER_USER_ID VARCHAR2(100 BYTE) NOT NULL,

VENDOR_SITE_CODE VARCHAR2(15 BYTE) NOT NULL,

TERM_DUE_PERCENT NUMBER,

TERM_DISCOUNT_MONTHS_FORWARD NUMBER,

TERM_DUE_DAYS NUMBER,

TERM_DISCOUNT_PERCENT NUMBER,

TERM_DISCOUNT_DAYS NUMBER,

TERM_DISCOUNT_DAY_OF_THE_MONTH NUMBER,

TERM_IDENTIFIER VARCHAR2(2 BYTE),

CURRENCY_CODE VARCHAR2(15 BYTE) NOT NULL,

ITEM_DESCRIPTION VARCHAR2(240 BYTE) NOT NULL,

UNIT_PRICE NUMBER NOT NULL,

SKU_NUMBER NUMBER NOT NULL,

TAXABLE_FLAG VARCHAR2(3 BYTE) NOT NULL,

QUANTITY_ORDERED NUMBER NOT NULL,

QUANTITY_RECEIVED NUMBER NOT NULL,

QUANTITY_SHIPPED NUMBER NOT NULL,

SHIPMENT_NUM VARCHAR2(100 BYTE) NOT NULL,

UOM VARCHAR2(25 BYTE) NOT NULL,

SHIP_TO_LOCATION NUMBER NOT NULL,

SET_OF_BOOKS_ID NUMBER NOT NULL,

GL_ACCRUAL_SEGMENT1 VARCHAR2(25 BYTE) NOT NULL,

GL_ACCRUAL_SEGMENT2 VARCHAR2(25 BYTE) NOT NULL,

GL_ACCRUAL_SEGMENT3 VARCHAR2(25 BYTE) NOT NULL,

GL_ACCRUAL_SEGMENT4 VARCHAR2(25 BYTE) NOT NULL,

GL_ACCRUAL_SEGMENT5 VARCHAR2(25 BYTE) NOT NULL,

GL_ACCRUAL_SEGMENT6 VARCHAR2(25 BYTE),

CREATION_DATE DATE NOT NULL,

EXTRACT_FLAG VARCHAR2(3 BYTE),

REJECT_REASON VARCHAR2(240 BYTE),

EXTRACT_DATE VARCHAR2(10 BYTE),

GOODS_RECEIVED_DATE VARCHAR2(20 BYTE) NOT NULL,

BOL_NUMBER VARCHAR2(30 BYTE),

TAX_PROVINCE VARCHAR2(2 BYTE) NOT NULL,

TAX_GST VARCHAR2(1 BYTE),

TAX_QST VARCHAR2(1 BYTE),

TAX_HST VARCHAR2(1 BYTE),

RETEK_SHIPMENT NUMBER,

DISCOUNT1_PCT NUMBER,

DISCOUNT1_NAME VARCHAR2(19 BYTE),

DISCOUNT1_CODE VARCHAR2(4 BYTE),

DISCOUNT2_PCT NUMBER,

DISCOUNT2_NAME VARCHAR2(19 BYTE),

DISCOUNT2_CODE VARCHAR2(4 BYTE),

DISCOUNT3_PCT NUMBER,

DISCOUNT3_NAME VARCHAR2(19 BYTE),

DISCOUNT3_CODE VARCHAR2(4 BYTE),

DISCOUNT4_PCT NUMBER,

DISCOUNT4_NAME VARCHAR2(19 BYTE),

DISCOUNT4_CODE VARCHAR2(4 BYTE),

DISCOUNT5_PCT NUMBER,

DISCOUNT5_NAME VARCHAR2(19 BYTE),

DISCOUNT5_CODE VARCHAR2(4 BYTE),

DISCOUNT6_PCT NUMBER,

DISCOUNT6_NAME VARCHAR2(19 BYTE),

DISCOUNT6_CODE VARCHAR2(4 BYTE),

DISCOUNT7_PCT NUMBER,

DISCOUNT7_NAME VARCHAR2(19 BYTE),

DISCOUNT7_CODE VARCHAR2(4 BYTE),

DISCOUNT8_PCT NUMBER,

DISCOUNT8_NAME VARCHAR2(19 BYTE),

DISCOUNT8_CODE VARCHAR2(4 BYTE),

DISCOUNT9_PCT NUMBER,

DISCOUNT9_NAME VARCHAR2(19 BYTE),

DISCOUNT9_CODE VARCHAR2(4 BYTE),

DISCOUNT10_PCT NUMBER,

DISCOUNT10_NAME VARCHAR2(19 BYTE),

DISCOUNT10_CODE VARCHAR2(4 BYTE),

DISCOUNT11_PCT NUMBER,

DISCOUNT11_NAME VARCHAR2(19 BYTE),

DISCOUNT11_CODE VARCHAR2(4 BYTE),

DISCOUNT12_PCT NUMBER,

DISCOUNT12_NAME VARCHAR2(19 BYTE),

DISCOUNT12_CODE VARCHAR2(4 BYTE),

DISCOUNT13_PCT NUMBER,

DISCOUNT13_NAME VARCHAR2(19 BYTE),

DISCOUNT13_CODE VARCHAR2(4 BYTE),

DISCOUNT14_PCT NUMBER,

DISCOUNT14_NAME VARCHAR2(19 BYTE),

DISCOUNT14_CODE VARCHAR2(4 BYTE),

DISCOUNT15_PCT NUMBER,

DISCOUNT15_NAME VARCHAR2(19 BYTE),

DISCOUNT15_CODE VARCHAR2(4 BYTE),

MASTER_BOL_NUMBER VARCHAR2(30 BYTE),

PO_APPROVE_DATE DATE NOT NULL,

STATUS VARCHAR2(30 bytes),

LAST_UPDATE_DATE DATE

)

ALTER TABLE XXHBC_RECEIPTS ADD (

CONSTRAINT CHECK_CURRENCY_CODE

CHECK (CURRENCY_CODE='USD' or CURRENCY_CODE='CAD'));

ALTER TABLE XXHBC_RECEIPTS ADD (

CONSTRAINT CHECK_EXTRACT_FLAG

CHECK (EXTRACT_FLAG ='Y' or EXTRACT_FLAG ='R' or EXTRACT_FLAG is null ));

ALTER TABLE XXHBC_RECEIPTS ADD (

CONSTRAINT CHECK_GL_ACCRUAL_SEGMENT6

CHECK (GL_ACCRUAL_SEGMENT6='000000' or GL_ACCRUAL_SEGMENT6 is null));

ALTER TABLE XXHBC_RECEIPTS ADD (

CONSTRAINT CHECK_TAXABLE_FLAG

CHECK (taxable_flag='Y' or taxable_flag='N'));

ALTER TABLE XXHBC_RECEIPTS ADD (

CONSTRAINT CHECK_TAX_GST

CHECK (TAX_GST='Y' or TAX_GST='N' or TAX_GST is null ));

ALTER TABLE XXHBC_RECEIPTS ADD (

CONSTRAINT CHECK_TAX_HST

CHECK (TAX_HST='Y' or TAX_HST='N' or TAX_HST is null ));

ALTER TABLE XXHBC_RECEIPTS ADD (

CONSTRAINT CHECK_TAX_PROVINCE

CHECK (TAX_PROVINCE ='AB'

or TAX_PROVINCE ='BC'

or TAX_PROVINCE='MB'

or TAX_PROVINCE='NB'

or TAX_PROVINCE='NF'

or TAX_PROVINCE='NS'

or TAX_PROVINCE='ON'

or TAX_PROVINCE='PE'

or TAX_PROVINCE='QE'

or TAX_PROVINCE='SK'));

ALTER TABLE XXHBC_RECEIPTS ADD (

CONSTRAINT CHECK_TAX_QST

CHECK (TAX_QST='Y' or TAX_QST='N' or TAX_QST is null ));

ALTER TABLE XXHBC_RECEIPTS ADD (

CONSTRAINT CHECK_UNIT_PRICE

CHECK (unit_price>0));

ALTER TABLE XXHBC_RECEIPTS ADD (

CONSTRAINT CHECK_UOM

CHECK (UOM='EACH'));

XXHBC_RECEIPTS_TESTERS table definition

Table XXHBC_RECEIPTS_TESTERS will have same fields as XXHBC_RECEIPTS table. It will hold only tester SKUS that were removed from the receipt.

Logic Summary

Loop for all XXHBC_RECEIPTS records where UNIT_PRICE < .01

Copy this record to XXHBC_RECEIPTS_TESTERS

Delete this record from XXHBC_RECEIPTS

End loop

COMMIT

Loop for all XXHBC_RECEIPTS records where STATUS = null

If PO_APPROVE_DATE >= edi810_effective_date (from xxhbc_sites_all for SITE)

Set STATUS = ‘3WAY’

Else set STATUS = ‘ERS’

End loop

COMMIT

Loop XXHBC_RECEIPTS records where STATUS = ‘3WAY’ or ‘ERS’

If STATUS = ‘3WAY’

Insert record into XXHBC_3WAY_PO_SHIP_IFACE

Set STATUS = ‘PROCESSED 3WAY’

Else STATUS = ‘ERS’

Calculate TAX based on tax flags and province

Calculate off-po discount

Calculate TAX for off-PO discount

Insert Invoice and off-po adjustments into AP OIT tables

Set STATUS = ‘PROCESSED ERS’

End if

Set Last_update_date = Sysdate

End loop

Loading ERS invoices to AP Open Interface tables

Oracle AP Open Interface tables must be populated creating ERS invoices. This is done to create invoices based on interface table xxhbc_Receipts STATUS = ‘ERS’.

OFF_PO adjustments (Debits)

If there are off-po discounts on the xxhbc_receipts table, these must be created on the AP Open Interface as separate invoices (Debits). Set Transaction code (Attribute4) on AP_INVOICES_INTERFACE to ‘900’ for all off-po adjustments. If taxes apply to the SKU, calculate it and insert it as part of the off-po debit

Invoice header DESCRIPTION for these adjustments need to have the DISCOUNT_NAME and DISCOUNT_PERCENTAGE.

Invoice_num for the adjustments are to have the ERS Invoice_number||AD# where # is the discount sequence.

Tax calculations

Calculate the tax amount on the invoice based on the Province code and tax type coming in on the xxhbc_receipts table. Date used for Rate is the Goods_received_date on the receipt. Look up the rate on the AP_Tax tables to get current tax code and calculate the amount. Taxes also need to be calculated for the off-PO adjustments.

Taxes are to be derived by SKU then summarized on invoice by tax type. Use TAX_PROVINCE, TAX_GST, TAX_QST, TAX_HST on each detail SKU record to get the tax code. Use the code to get the rate for the applicable tax. Calculate tax amount based on this rate. Summarize the item (sku) taxes by tax type and create a TAX line on Invoice distribution for each type.

When determining TAX code/rate, if you are not able to find correct combination in TAX tables, mark the whole receipt (PO/shipment/location) with status = ‘TAX ERROR’

TABLE: AP_INVOICES_INTERFACE

Column	Value
INVOICE_ID	ap_invoices_interface_s.NEXTVAL
VENDOR_ID	Derived from VENDOR_SITE_CODE
VENDOR_NUM	Derived from VENDOR_SITE_CODE
VENDOR_SITE_ID	Derived from VENDOR_SITE_CODE
INVOICE_NUM	First 7 bytes of PO number\|\|first 10 bytes of Shipment_num (ASN number)\|\|ship_to_location (all from xxhbc_receipts table). Off_po adjustments have this concatenated with AD# where # is the adjustment sequence. Note Invoice Number can not have spaces.
INVOICE_AMOUNT	Total of detail amounts
EXCHANGE_RATE_TYPE	SELECT conversion_type FROM gl_daily_conversion_types WHERE user_conversion_type = 'IMPORT' and rownum = 1;
EXCHANGE_DATE	Use procedure APPS.xxhbcciu_ap_interface .populate_exchange_date
SOURCE	‘RETEK ZELLERS’
GROUP_ID	This identified the whole interface file. All invoices on the file will have the same group_id. Set to: ‘ERS’\|\|CCYYMMDDHHMMSS (current sysdate)
VOUCHER_NUM	Set to: Group_id\|\|’ ‘\|\|invoice_num
TERMS_ID	Derive terms from Vendor site terms_id
INVOICE_RECEIVED_DATE	Xxhbc_receipts.creation_date
GL_DATE	Xxhbc_receipts.creation_date
ATTRIBUTE_CATEGORY	'Transaction Code'
ATTRIBUTE4 -- HBC AP Inv Transaction Code	401 (900 for off_po adjustments)
GOODS_RECEIVED_DATE	Xxhbc_receipts.goods_received_date
TERMS_DATE	Set to same value as GOODS_RECEIVED_DATE

DESCRIPTION	(Discount description)}\|\|‘PO=ppppppppp ASN=aaaaaaaa Loc=llllllllllll’ Where pppppppp= Xxhbc_receipts.po_number, aaaaaaa = Xxhbc_receipts.shipment_num, llllllll= Xxhbc_receipts.ship_to_location
LAST_UPDATED_BY	User
LAST_UPDATE_DATE	Sysdate
CREATION_DATE	Sysdate
CREATED_BY	User

TABLE: AP_INVOICE_LINES_INTERFACE

Column	Value
INVOICE_ID	ap_invoices_interface_s.CURVAL
INVOICE_LINE_ID	ap_invoice_lines_interface_s.NEXTVAL
LINE_NUMBER	Seq starting from 1
LINE_TYPE_LOOKUP_CODE	‘ITEM’ or ‘TAX’ depending on line type
TAX_CODE	For ‘TAX’ lines, check 3way match code for logic on how to derive the tax code.
DIST_CODE_COMBINATION_ID	Derive from 6 segments on xxhbc_receipts
DIST_CODE_CONCATENATED
DESCRIPTION	‘PO=ppppppppp ASN=aaaaaaaa Loc=llllllllllll UPC/SKU=uuuuuuu/sssssss’ (get all values from Xxhbc_receipts recordl to help identify the invoice line) UPC is the Ordering UPC from Retek SKU/UPC mapping table (apps.UPC_EAN). Where pppppppp= Xxhbc_receipts.po_number, aaaaaaa = Xxhbc_receipts.shipment_num, llllllll= Xxhbc_receipts.ship_to_location, sssssss=SKU
LAST_UPDATED_BY	User
LAST_UPDATE_DATE	Sysdate
CREATION_DATE	Sysdate
CREATED_BY	User
AMOUNT	Unit_price * quantity_received. For TAX records derive based on Rate. Summarize taxes by type to make sure there is just one TAX line per type.
GLOBAL_ATTRIBUTE2	Quantity_invoiced
GLOBAL_ATTRIBUTE1	Unit_price

Payment remittance process ELECTRONIC (EDI820) – xxhbc_3way_edi820_pkg

Payment remittances will be sent out to vendors via EDI820 transactions on a daily basis. This remittance will include all checks generated on that day. The Concurrent Job will also allow users to run the request and specify if a check needs to be resent (from another day) to the vendor via EDI820.

This process describes ELECTRONIC EDI820 remittances. Paper remittances are described later in the document.

*** THIS PROCESS IS A MODIFICATION TO EXISTING EDI820 PROCESS DONE FOR 3WAY VENDORS CURRENTLY ***

Modify the existing EDI820 program (xxhbc_3way_edi820_pkg) to accept a check number (USD or CAD) to be re-extracted to EDI820 file. Make sure the check number is a valid issued check. This means the CM job will allow this in ADDITION to the date parameter. If a date is specified, extract ALL checks on that date. If a check number is specified, only extract that check, regardless of the date it was created.

The extract will concatenate the date time stamp to the filename output. This will allow multiple files of EDI820 per day.

EDI transactions are created based on the checks generated and the detail (invoices and adjustments) that make up that check.

Each invoice on the check needs to have an A* entry or R* entries. Not both.

· The following invoices require R* EDI820 entries:

TranType (Attribute4) = 401 and source in (‘EDI 810’, ’RETEK ZELLERS’)

TranType (Attribute4) = 900 and source in (’RETEK ZELLERS’)

TranType (Attribute4) = 401 and source in (‘RETEK CONSIGNMENT BAY’, ‘RETEK CONSIGNMENT ZELLERS’) *** Special EDI820 mapping based on Retek View for details***

· The rest of the invoices require just the A* EDI820 entry.

· Only create records where the vendor is marked as EDI820 remittance method of Electronic or Both. Paper remittances are described later in the document and are produced as a report to be sent to vendors.

The hierarchy of the transactions is as follows:

H1 (one per Check)

D1 (one per Check)

--- A1 (Manual debits, Volume Rebates, etc) (One per Debit note) ALL invoices NOT part of the R1 selection

--- R1 (Merchandise invoices, Off-PO adjustments) (One per invoice/PO) ALL 401 (EDI 810 and RETEK ZELLERS) and 900 (RETEK ZELLERS) and Consignment invoices

-------- R2 (Invoice item details) (One per invoice line/SKU) ALL 401 ITEM lines and Consignment invoices ITEM lines

-------- R3 (non-merchandise items [e.g. Taxes] and merchandise adjustments) (One per invoice line/SKU) ALL 401 TAX lines and ALL 900 lines and Consignment invoices TAX lines

-------- R4 (reason for adjustment) Based on 900 ITEM lines

-------- R5 (only if R3 record is a merchandise item adjustment, this record includes SKU item information) Based on 900 ITEM lines

Generally, if the invoice is Transaction code 401 (merchandise invoices Retek or EDI810) or 900 (off-po adjustments) the R* level transactions are required, for other adjustments A1 transaction is used~~. If an invoice has detail lines (AP_INVOICE_DISTRIBUTIONS_ALL) with a PO_distribution_id, the R* transactions are needed, otherwise the A1 is required.~~

ERS invoices and adjustments will have details required for R* records. Consignment invoices will have different R1, R2 and R3 mappings from ERS and EDI810 invoices.

The R*and A1 transaction amounts should add up to the Check amount reported on H1 transaction.

The EDI process will be done in two stages because EDI requires a mainframe conversion of the signed number fields. What follows is the detailed description of the Oracle interface of remittances.

All number fields should be zero filled (at left) and if signed, include a leading space indicating the sign of the number (‘-‘ or ‘+’). For amounts having dollars and cents, multiply by 100 to remove the decimal.

The mainframe record length of EDI820 records is 250 bytes.

ONLY create output for vendor sites where xxhbc_sites_all.EDI820_METHOD in (‘Electronic, ‘Both’)

Note:

The relationship between 3way MATCHED Invoice and PO is:

po_distributions_all.PO_distribution_id = AP_INVOICE_DISTRIBUTIONS_ALL.PO_distribution_id

H1 – EDI820 - Remittance Header Record (1 Record per Remittance Advice/Check)

Element	Attributes	Comments	Req	Source Value
Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'H1 '		Y
EDI Client	x(10) value ‘THE BAY ‘ ‘ZELLERS ‘ ‘BAYSPEC ‘		Y	‘ZELLERS ‘
EDI Partner	x(6)	vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '0'		Y
Filler	9(18) value '0'		Y

Data Group
VICS Transaction Code	x(1) value ‘I’
Check Amount	9(9)v99	* 100 to remove decimals, zero fill		ap_checks.amount
VICS Credit Flag	x(1) value ‘C’
VICS Pmt Method Code	x(3) value ‘CHK’
VICS Pmt Format Code	x(3) value ‘PBC’
Check Date	9(8)	CCYYMMDD		ap_checks.check_date
VICS Check Qualifier	x(2) value ‘CK’

D1 – EDI820 - Remittance Vendor Detail Record (1 Record per Vendor Remittance Advice)

Element	Attributes	Comments	Req	Source Value
Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'D1 '		Y
EDI Client	x(10) value ‘THE BAY ‘ ‘ZELLERS ‘ ‘BAYSPEC ‘		Y	‘ZELLERS ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '1'		Y
Assigned Id.	9(6)	Sequentially incrementing nbr. For each pmt adjustment (or remittance pmt). Starting with ‘1’	Y	‘1’ – since there will only be one of these per check
Filler	9(12) value '0'		Y

Data Group
Filler	x(198)			spaces

A1 – EDI820 - Payment Adjustment Detail Record (1 Record per non-PO Adjustment)

~~Invoice DOES NOT have detail lines (AP_INVOICE_DISTRIBUTIONS_ALL) with a PO_distribution_id~~

Invoice Transaction code (Attribute4) NOT = ‘900’ or ‘401’

Element	Attributes	Comments	Req	Source Value

Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'A1 '		Y
EDI Client	x(10) value ‘THE BAY ‘ ‘ZELLERS ‘ ‘BAYSPEC ‘		Y	‘ZELLERS ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check Nbr.	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '2'		Y
Assigned ID	9(6)		Y	000001
Filler	9(12) value '0'		Y

Data Group
Payment Adjustment Amount	9(9)v99			Abs(ap_invoices_all.paid_amount)
VICS Adjustment Reason Code	x(2)			‘ZZ’
Adjustment Adx Ref Qual Code	x(2)			If ap_invoices_all.paid_amount < 0 then = ‘DB’ else = ‘CM’
Adjustment Adx Ref ID Nbr.	x(25)	Invoice #		ap_invoices_all.invoice_number
Debit-Credit Ref Qual. Code #1	x(2) value ‘CM’ or ‘DB’	CM=credit memo DB=debit memo		If ap_invoices_all.paid_amount < 0 then = ‘DB’ else = ‘CM’
Debit-Credit Ref Text #1	x(30)			ap_invoices_all.source \|\| first part of ap_invoices_all.description
VICS Adjustment Date Qual.	x(3) value ‘097’
Adjustment Transaction Date	9(8)	CCYYMMDD		ap_invoices_all.invoice_date
Debit-Credit Ref Qual. Code #2	x(2) value ‘CM’ or ‘DB’			If ap_invoices_all.paid_amount < 0 then = ‘DB’ else = ‘CM’ (*only need if ‘Debit-Credit Ref Text #2’* is NOT NULL, otherwise, set to null)**
Debit-Credit Ref Text #2	X(30)			Remaining part of ap_invoices_all.description

R1 – EDI820 - Remittance Payment Detail Record (1 Record per remittance payment)

~~R* is needed ONLY if invoice has detail lines (AP_INVOICE_DISTRIBUTIONS_ALL) with a PO_distribution_id~~

OR ~~‘RETEK ZELLERS’~~

Invoices with Invoice Transaction code (Attribute4) = ‘900’ or ‘401’

Element		Attributes	Comments	Req		Source Value
Key Group
EDI Transaction Set Code		x(3) value '820'		Y
EDI Record Type Code		x(7) value 'R1 '		Y
EDI Client		x(10) value ‘THE BAY ‘ ‘ZELLERS ‘ ‘BAYSPEC ‘		Y		‘ZELLERS ‘
EDI Partner		x(6)	Zellers vendor nbr	Y		ap_checks_all.vendor_site_code
Check nbr		9(7)		Y		ap_checks_all.check_number
Application Record Type Code		x(1) value '4'		Y
Assigned Id Nbr		9(6)	sequentially incrementing number for each remittance payment (or payment adjustment) starting with 1.	Y
Filler		9(12) value '0'		Y

Data Group
VICS Vendor Invoice Qualifier		x(2) value 'IV'
Vendor Invoice Nbr.		x(25)				ap_invoices_all.invoice_number
VICS Payment Action Code		x(2) value 'ER' or ‘PO’	ER = evaluated receipts PO = Payment on Account			‘PO’
Amount Paid		S9(9)v99				ap_invoices_all.Amount_paid
Total Credit-Debit Amt.		S9(9)v99				ap_invoices_all.Invoice_amount
Discount Amount		S9(9)v99				ap_invoices_all.Discount_amount_taken
VICS Ship Id. Qualifier		x(2) value ‘SI’ or ‘BM’				‘SI’ for ASN vendor, ‘BM’ for BOL vendor (Note only 3way vendors use BOL, ERS always uses ASN)
ASN Number		x(10)				FOR non-BOL vendor, derive shipment number from invoice description (Note only 3way vendors use BOL, ERS always uses ASN)
VICS Ship Id. Qualifier		x(2) value ‘SI’
ASN Number		x(10)				Derive from Invoice description
VICS Remittance Date Qualifier		x(3) value ‘097’
Remittance Date		9(8)	CCYYMMDD			ap_invoices_all.invoice_date
VICS PO Id. Qualifier		x(2) value ‘PO’ or ‘ST				‘PO’
PO Number / Store #		x(11)	Store used for Sbt			Derive PO Number from Invoice description
REF BOL #	x(20)				FOR BOL vendor: Use po_line_locations_all.shipment_num and the site number to look up the original ASN on XXHBC_3WAY_PO_SHIP_XREF table. (Note only 3way vendors use BOL, ERS always uses ASN)

R2 – EDI820 - Remittance Payment Item Record (1 Record per Item, per Remittance Payment)

~~One record for every line_type_lookup_code=‘ITEM’ and quantity_invoiced > 0 on the AP_INVOICE_DISTRIBUTIONS_ALL for the invoice~~

~~(This is the original invoice information; all the adjustments will be on separate records)~~

line_type_lookup_code=‘ITEM’ and Invoice Transaction code (Attribute4) =‘401’

Element	Attributes	Comments	Req	Source Value

Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'R2 '		Y
EDI Client	x(10) value ‘THE BAY ‘ ‘ZELLERS ‘ ‘BAYSPEC ‘		Y	‘ZELLERS ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '5'		Y
Assigned Id	same as on R1 record		Y
Filler	9(6) value '0'		Y
Item Assigned Id.	9(6)	sequentially incrementing nbr. for each item within a remittance pmt. starting at ‘1’	Y

Data Group
Quantity Invoiced	S9(7)		Y	AP_INVOICE_DISTRIBUTIONS_ALL. GLOBAL_ATTRIBUTE2
VICS Unit of Measure	x(4)	e.g. each	Y	‘EACH’
Unit Price	9(9)v99			AP_INVOICE_DISTRIBUTIONS_ALL. GLOBAL_ATTRIBUTE1
VICS UPC/EAN Qualifier	x(2) value ‘UP’ or ‘EN’	use UP for UPC, use EN for EAN		‘UP’
UPC or EAN Nbr	x(13)	when a UPC nbr. is specified, then a trailing space should appear in the right-most position		Derive from AP_INVOICE_DISTRIBUTIONS_ALL.description
VICS SKU Qualifier	x(2) value ‘IN’
SKU Number	9(8)			Derive from Invoice distribution description
VICS Style Qualifier	x(2) value ‘IT’
Style Number	x(20)
VICS Colour Qualifier	x(2) value ‘BO’
Colour Description	x(10)
VICS Size Code	x(2) value ‘IZ’
Size Description	x(10)

R3 – EDI820 - Remittance Payment Adjustment Record

~~(These are adjustments to the vendor invoice [line_type_lookup_code=‘ITEM’ and quantity_invoiced < 0] and non-ITEM lines)~~

(line_type_lookup_code=‘TAX’ and Invoice Transaction code (Attribute4) =‘401’)

AND (all Invoice Transaction code (Attribute4) =‘900’ lines)

Element	Attributes	Comments	Req	Source Value

Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'R3 '		Y
EDI Client	x(10) value ‘THE BAY ‘ ‘ZELLERS ‘ ‘BAYSPEC ‘		Y	‘ZELLERS ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '6'		Y
Assigned Id	9(6)	same as in R1 record	Y
Remittance Adjustment Assigned Id	9(6) value ‘1’	sequentially incrementing nbr. for each adjustment within a remittance pmt. starting at ‘1’	Y
Filler	9(6) value ‘0’		Y

Data Group
Remittance Adjustment Amount	S9(9)v99			AP_INVOICE_DISTRIBUTIONS_ALL.amount
X12 Adjustment Reason Code	x(2)	‘BA’ Gst ‘BB’ Qst ‘BC’ Hst ‘L2’ Discount ‘L3’ Penalty or Charged ‘L7’ Credit quantity desc on Asn > actual qty ‘L8’ Debit quantity desc on Asn < actual qty		For tax lines, select the correct tax type. (use AP_TAX_CODES_ALL table to link the CCIDs to determine tax type). Most times will = ‘L3’

R4 – EDI820 - Remittance Payment Adjustment Notes Record

~~This has the item information for the adjusted item (only for matched po_line_locations_all items)~~

~~(line_type_lookup_code=‘ITEM’ and quantity_invoiced < 0)~~

line_type_lookup_code=‘TAX’ and Invoice Transaction code (Attribute4) = ‘900’

Element	Attributes	Comments	Req	Source Value

Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'R4 '		Y
EDI Client	x(10) value ‘THE BAY ‘ ‘ZELLERS ‘ ‘BAYSPEC ‘		Y	‘ZELLERS ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '7'		Y
Assigned Id	9(6)	same as R1 record	Y
Remittance Adjustment Assigned Id	9(6)	same as R3 record	Y
Filler	9(6) value ‘0’		Y

Data Group
Remittance Adjustment Description	x(60)			AP_INVOICE_DISTRIBUTIONS_ALL.description

R5 – EDI820 - Remittance Payment Adjustment Item Record (1 Record per Item, per Remittance Adjustment, per Remittance Pmt)

This is the detail to support R4 record

line_type_lookup_code=‘ITEM’ and Invoice Transaction code (Attribute4) = ‘900’

Element	Attributes	Comments	Req	Source Value

Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'R5 '		Y
EDI Client	x(10) value ‘THE BAY ‘ ‘ZELLERS ‘ ‘BAYSPEC ‘		Y	‘ZELLERS ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '8'		Y
Assigned Id	9(6)	same as R1	Y
Remittance Adjustment Assigned Id	9(6)	same as R3	Y
Item Assigned Id.	9(6) value ‘1’	sequentially incrementing nbr. for each item within an adjustment for a remittance pmt. starting at ‘1’	Y

Data Group
Quantity Invoiced	S9(7)		Y	AP_INVOICE_DISTRIBUTIONS_ALL. GLOBAL_ATTRIBUTE2
VICS Unit of Measure	x(4)	e.g. each	Y	‘EACH’
Unit Price	9(9)v99			AP_INVOICE_DISTRIBUTIONS_ALL. GLOBAL_ATTRIBUTE1
VICS UPC/EAN Qualifier	x(2) value ‘UP’ or ‘EN’	use UP for UPC, use EN for EAN		‘UP’
UPC or EAN Nbr	x(13)	when a UPC nbr. is specified, then a trailing space should appear in the right-most position		Derive from AP_INVOICE_DISTRIBUTIONS_ALL.description
VICS SKU Qualifier	x(2) value ‘IN’
SKU Number	9(8)			Get SKU number from Invoice detail description
VICS Style Qualifier	x(2) value ‘IT’
Style Number	x(20)
VICS Colour Qualifier	x(2) value ‘BO’
Colour Description	x(10)
VICS Size Code	x(2) value ‘IZ’
Size Description	x(10)

Consignment invoice EDI820 mapping

All Consignment invoices will have EDI820 records. These invoices have source = ‘RETEK CONSIGNMENT BAY’ or ‘RETEK CONSIGNMENT ZELLERS’. EDI820 H1 and D1 mapping is the same as above since these are Check level records. R1, R2, R3 mapping is described below. Consignment invoices will NOT have A1, R4 and R5 records.

When a check contains any invoices with source = ‘RETEK CONSIGNMENT BAY’ or ‘RETEK CONSIGNMENT ZELLERS’, use the below field mapping for generating the R1,2,3 records for these invoices.

A database link will be used to get the data from Retek Tables for the data.

Bay invoice details are in views: CONSIGN_820_REMIT_HDR_RTKBRO_V (Header) and CONSIGN_820_REMIT_DTL_RTKBRO_V (Detail)

Zellers invoice details are in views: CONSIGN_820_REMIT_HDR_V (Header) and CONSIGN_820_REMIT_DETAIL_V (Detail).

Join by SUPPLIER (Site number), CHECK_NO (Check number), INVOICE_NO (Invoice number). Check one view for the invoice, if not there, check the other. If invoice is not on either view, get data from AP invoice record.

Use the views to get required invoice data based on the AP invoice in Oracle. Most of the details will come from the views for EDI820 creation.

R1 – EDI820 - Remittance Payment Detail Record (1 Record per remittance payment) CONSIGNMENT

source = ‘RETEK CONSIGNMENT BAY’ or ‘RETEK CONSIGNMENT ZELLERS’

Element	Attributes	Comments	Req	Source Value
Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'R1 '		Y
EDI Client	x(10) value ‘THE BAY ‘ ‘ZELLERS ‘ ‘BAYSPEC ‘		Y	‘ZELLERS ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '4'		Y
Assigned Id Nbr	9(6)	sequentially incrementing number for each remittance payment (or payment adjustment) starting with 1.	Y
Filler	9(12) value '0'		Y

Data Group
VICS Vendor Invoice Qualifier	x(2) value 'IV'
Vendor Invoice Nbr.	x(25)			ap_invoices_all.invoice_number
VICS Payment Action Code	x(2) value 'ER' or ‘PO’	ER = evaluated receipts PO = Payment on Account		‘PO’
Amount Paid	S9(9)v99			ap_invoices_all.Amount_paid
Total Credit-Debit Amt.	S9(9)v99			ap_invoices_all.Invoice_amount
Discount Amount	S9(9)v99			ap_invoices_all.Discount_amount_taken
VICS Ship Id. Qualifier	x(2) value ‘SI’
ASN Number	x(10)
VICS Remittance Date Qualifier	x(3) value ‘097’
Remittance Date	9(8)	CCYYMMDD		ap_invoices_all.invoice_date
VICS PO Id. Qualifier	x(2) value ‘PO’ or ‘ST			‘PO’
PO Number / Store #	x(11)	Store used for Sbt		VIEW HEADER . STORE

R2 – EDI820 - Remittance Payment Item Record (1 Record per Item, per Remittance Payment) CONSIGNMENT

source = ‘RETEK CONSIGNMENT BAY’ or ‘RETEK CONSIGNMENT ZELLERS’ line_type_lookup_code=‘ITEM’

Element	Attributes	Comments	Req	Source Value

Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'R2 '		Y
EDI Client	x(10) value ‘THE BAY ‘ ‘ZELLERS ‘ ‘BAYSPEC ‘		Y	‘ZELLERS ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '5'		Y
Assigned Id	same as on R1 record		Y
Filler	9(6) value '0'		Y
Item Assigned Id.	9(6)	sequentially incrementing nbr. for each item within a remittance pmt. starting at ‘1’	Y

Data Group
Quantity Invoiced	S9(7)		Y	VIEW DETAIL. UNITS
VICS Unit of Measure	x(4)	e.g. each	Y	‘EACH’
Unit Price	9(9)v99			VIEW DETAIL. UNIT_COST
VICS UPC/EAN Qualifier	x(2) value ‘UP’ or ‘EN’	use UP for UPC, use EN for EAN		‘UP’
UPC or EAN Nbr	x(13)	when a UPC nbr. is specified, then a trailing space should appear in the right-most position		VIEW DETAIL. UPC
VICS SKU Qualifier	x(2) value ‘IN’
SKU Number	9(8)			VIEW DETAIL. SKU
VICS Style Qualifier	x(2) value ‘IT’
Style Number	x(20)
VICS Colour Qualifier	x(2) value ‘BO’			VIEW DETAIL. COLOUR_DESC
Colour Description	x(10)
VICS Size Code	x(2) value ‘IZ’			VIEW DETAIL. SIZE_DESC
Size Description	x(10)

R3 – EDI820 - Remittance Payment Adjustment Record CONSIGNMENT

source = ‘RETEK CONSIGNMENT BAY’ or ‘RETEK CONSIGNMENT ZELLERS’ line_type_lookup_code=‘TAX’

Element	Attributes	Comments	Req	Source Value

Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'R3 '		Y
EDI Client	x(10) value ‘THE BAY ‘ ‘ZELLERS ‘ ‘BAYSPEC ‘		Y	‘ZELLERS ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '6'		Y
Assigned Id	9(6)	same as in R1 record	Y
Remittance Adjustment Assigned Id	9(6) value ‘1’	sequentially incrementing nbr. for each adjustment within a remittance pmt. starting at ‘1’	Y
Filler	9(6) value ‘0’		Y

Data Group
Remittance Adjustment Amount	S9(9)v99			AP_INVOICE_DISTRIBUTIONS_ALL.amount
X12 Adjustment Reason Code	x(2)	‘BA’ Gst ‘BB’ Qst ‘BC’ Hst ‘L2’ Discount ‘L3’ Penalty or Charged ‘L7’ Credit quantity desc on Asn > actual qty ‘L8’ Debit quantity desc on Asn < actual qty		For tax lines, select the correct tax type. (use AP_TAX_CODES_ALL table to link the CCIDs to determine tax type). Most times will = ‘L3’

When to Run the Program

Daily

Launch Parameters

CM request – XXHBC 3 Payment Remittance outbound EDI820

Parameters :

Date

Check Number

Restart Procedures

TBD

Payment remittance process PAPER (EDI820) – xxhbc_edi820_remittance

Some vendors can not receive Electronic EDI820 remittances or would like to receive paper EDI820 data as well as electronic. These vendors are marked xxhbc_sites_all.EDI820_METHOD ‘Paper’ or ‘Both’ . For these vendors we need to create a report to send to them by mail.

HUDSON’S BAY Cheque # F1

820 REMITANCE ADVICE Date : F23

Paid To: Page : F2

F6 F8 Vendor Site # F10

F7 F9

DOCUMENT # Description PO # Date UPC/SKU QTY Rec Unit Price Total

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

F11 F12 F13 F14 F15 F16 F17 F18

Subtotal F19

GST/ HST F20

QST F21

Total Before Terms F22

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

- Page break on Vendor Site number and Check number

- Get details as on electronic EDI820 remittance

- Document number is Invoice Number. Repeat more than one document on a page if exists on the cheque.

- D12 is invoice header description

- D3-9 is vendor pay to address

- On each new vendor, first page is to contain ONLY the vendor address as shown in scan.

- See scan of current report for clarification of data and layout. This report is similar to this

When to Run the Program

Daily and on request

Launch Parameters

CM request – XXHBC Payment Remittance outbound EDI820 PAPER

Parameters :

Date

Check Number

Restart Procedures

TBD

Open and Closed Issues for this Deliverable

Open Issues

ID	Issue	Resolution	Responsibility	Target Date	Impact Date

Closed Issues

ID	Issue	Resolution	Responsibility	Target Date	Impact Date

CREATE OR REPLACE PACKAGE APPS.xxhbc_3way_all_receipt
AS
/* -------------------------------------------------------------------------- */
/* Program Name : XXHBC_3WAY_ALL_RECEIPT                         */
/*                                                                            */
/* TYPE         : PL/SQL Package                                             */
/*                                                                            */
/* Input Parms :                                                */
/*                                                                            */
/* Output Parms : --                                                         */
/*                                                                            */
/* Table Access : -- xxhbc_3way_po_ship_iface,xxhbc_sites_all                  */
/*                                                                            */
/* AUTHOR       : Chandra Sekhar Kadali                                      */
/*                                                                            */
/* DATE         : 11-JAN-2013                                                */
/*                                                                            */
/* VERSION      : 1.0                                                        */
/*                                                                            */
/* DESCRIPTION : XXHBC_3WAY_ALL_RECEIPTconcurrent program is used*/
/* to process all receipts from Retek on a daily base                       */
/*                                                                            */
/*                                                                            */
/* CHANGE HISTORY                                                            */
/* -------------------------------------------------------------------------- */
/* DATE        AUTHOR                          VERSION REASON                */
/* -------------------------------------------------------------------------- */
/* 17/01/2013 Chandra Sskhar Kadali          1.0      Initial creation      */
/* -------------------------------------------------------------------------- */
   PROCEDURE main(
      errbuf                     OUT      VARCHAR2
     ,retcode                    OUT      VARCHAR2);

   FUNCTION get_vendor_site_id(
      p_vendor_site_code                  VARCHAR2)
      RETURN NUMBER;

   FUNCTION get_vendor_id(
      p_vendor_site_code                  VARCHAR2)
      RETURN NUMBER;

   FUNCTION get_vendor_num(
      p_vendor_site_code                  VARCHAR2)
      RETURN VARCHAR2;

   FUNCTION get_terms_id(
      p_vendor_site_code                  VARCHAR2)
      RETURN NUMBER;

   FUNCTION get_exchange_rate_type
      RETURN VARCHAR2;

   FUNCTION get_dist_code_combination_id(
      p_segment1                          VARCHAR2
     ,p_segment2                          VARCHAR2
     ,p_segment3                          VARCHAR2
     ,p_segment4                          VARCHAR2
     ,p_segment5                          VARCHAR2
     ,p_segment6                          VARCHAR2)
      RETURN NUMBER;

   FUNCTION get_tax_code_concatenated(
      p_tax_ccid                          NUMBER)
      RETURN VARCHAR2;
      PROCEDURE submit_import(
      p_source                   IN       VARCHAR2
     --,p_group_id   IN VARCHAR2
   , p_batch_date               IN       VARCHAR2
   , p_request_id out number
   );
END xxhbc_3way_all_receipt;
/

Visitors

Tuesday, November 25, 2014

Thursday, October 09, 2014

Friday, September 26, 2014

Current Process

New Process

XXHBC_RECEIPTS table definition

XXHBC_RECEIPTS_TESTERS table definition

OFF_PO adjustments (Debits)

Tax calculations

Payment remittance process ELECTRONIC (EDI820) – xxhbc_3way_edi820_pkg

Payment remittance process PAPER (EDI820) – xxhbc_edi820_remittance