Changing data from one Character set to another with Unix Program using ADNCNV

Now it is time to tell you a story about Languages.

According to the Bible,Many a eons ago people decided to build a tower up-to the sky.But God wished otherwise.He caused them to speak different languages in order to confuse them among themselves.Those people,unable to understand one another,abandon their project and the tower remained unfinished.

Story ended now, please come into the technology world.

So multiple languages with multiple characters, some characters will be common among languages and some are not.

In Oracle data will be feed from different sources and not all systems use the same character set and hence the characters cannot be same..

One of the interesting AD utilities will convert data from One character set to another.

# character sets

P_SRC_CHAR_SET=UTF8 # Source Char set

P_DEST_CHAR_SET=WE8ISO8859P1 # Destination Char set (Western European char set)

# adncnv to convert from character set to another.

# $FILENAME is the source file name

# $DEST_FILENAME is the destination file name after conversion of the data.

adncnv $FILENAME $P_SRC_CHAR_SET $DEST_FILENAME $P_DEST_CHAR_SET

if [ $? -eq 0 ]
then
echo "Conversion Successful"
echo ""
else
echo "ERROR 10 - Conversion Errored out"
echo ""

Passing NULL Values for the TCA APIs

Sometimes we need to pass the NULL values to some of the attributes using TCA APIs.

e.g. HZ_LOCATIONS, Address Line3 and Address Line4 can be NULL and you wanted to explicitly make them to update a NULL value using the APIs.

Simply assigning a NULL to the variable will not make this value as NULL.

Use the above values depending on the datatype you wanted to make the value to NULL. These standard variables are part of fnd_api package so you need to prefix this with using the below syntax.

p_location_rec.address_line4       := fnd_api.g_null_char;

Please note that earlier G_MISS_XXX were there and now is replaced by G_NULL_XXX.

Scheduled Concurrent Program Details

Now it is time to explore what concurrent jobs are scheduled

This is my answer :-

This will give the concurrent program details, schedule frequency, from which responsibility they are executing and requester etc. etc..


     SELECT V.request_id,
         (SELECT flv_phase.meaning
            FROM fnd_lookup_values_vl flv_phase
           WHERE     1 = 1
                 AND flv_phase.lookup_type = 'CP_PHASE_CODE'
                 AND flv_phase.enabled_flag = 'Y'
                 AND flv_phase.lookup_code = v.phase_code
                 AND view_application_id = 0)
            phase,
         (SELECT flv_status.meaning
            FROM fnd_lookup_values_vl flv_status
           WHERE     1 = 1
                 AND flv_status.lookup_type = 'CP_STATUS_CODE'
                 AND flv_status.enabled_flag = 'Y'
                 AND flv_status.lookup_code = v.status_code
                 AND view_application_id = 0)
            status_code,
         V.request_date,
         fu.user_name requestor,fu.description,
         V.requested_start_date,
         fres.responsibility_name,
         V.parent_request_id,
         V.actual_start_date,
         V.actual_completion_date,
         DECODE (fcp.user_concurrent_program_name, 'Report Set', 'Report Set:' || V.description, fcp.user_concurrent_program_name) CONC_PROG_NAME,
         NVL2 (V.resubmit_interval, 'PERIODICALLY', NVL2 (V.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
         DECODE (NVL2 (V.resubmit_interval, 'PERIODICALLY', NVL2 (V.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
                 'PERIODICALLY', 'EVERY ' || V.resubmit_interval || ' ' || V.resubmit_interval_unit_code || ' FROM ' || V.resubmit_interval_type_code || ' OF PREV RUN',
                 'ONCE', 'AT :' || TO_CHAR ( V.requested_start_date, 'DD-MON-RR HH24:MI'),
                 'EVERY: ' || fcrc.class_info)
            PROG_SCHEDULE,
         V.argument_text
    FROM apps.fnd_conc_release_classes fcrc, FND_CONCURRENT_REQUESTS V, Fnd_concurrent_programs_vl fcp, fnd_user fu,fnd_responsibility_vl fres
   WHERE     phase_code = 'P'
         AND V.status_code IN ('I', 'Q')
         AND (NVL (V.request_type, 'X') != 'S')        
         AND fcrc.release_class_id(+) = V.release_class_id
         AND v.concurrent_program_id = fcp.concurrent_program_id
         AND v.requested_by = fu.user_id
         AND fcrc.application_id(+) = V.release_class_app_id
         and fres.responsibility_id=v.responsibility_id
ORDER BY REQUEST_ID  desc

PO Output for Communication

PO Output for Communication is one of the most important standard reports available in Oracle. Very often in every implementation project the layout of this report is customized to suit the customer needs and demands.

In this article, i will touch up some of the 'lessons learnt' issues

1- As per the Oracle documentation, this report will be triggered as soon as the Purchase Order is Approved. Also it will send a notification to the Supplier by Email as long as the Supplier Site's Communication Method is 'Email'

Sometimes suppliers might complain that they didn't receive the Purchase Order document from Oracle due to which delays the procurement of the goods. 

In R12 Oracle comes up with a profile option that can be set to a generic Email and the program will send a copy of PO PDF(output of this report) to this email address.

Navigate to: System Administrator=>Profiles=>System

Set the profile option PO: Secondary Email address to a generic Email address to which the purchase order will be sent.

This back up Email can be used for internal reference or to forward to supplier in case if it is missed somehow.

2-  The PO Output for Communication leverages the features of Oracle Workflow Mailer Engine to send Purchase Order PDF notification to the Suppliers.

The below query would be helpful to know whether the Purchase Order transmitted to the supplier or not.

SELECT segment1,
       approved_date,
       supplier_notif_method,
       polc.displayed_field po_status,
       print_count,
       printed_date,
       from_user,
       to_user,

       status,
       mail_status,
       original_recipient
  FROM po_headers_all poh, wf_notifications wn,po_lookup_codes polc
 WHERE     1=1
     AND polc.lookup_type(+)= 'AUTHORIZATION STATUS'
        AND poh.authorization_status = polc.lookup_code(+)
       AND wn.user_key(+) = poh.segment1
       AND wn.item_key(+) = poh.wf_item_key    
       AND supplier_notif_method = 'EMAIL'
       AND NVL (edi_processed_flag, 'N') <> 'Y'
       AND NVL(xml_flag,'N') <> 'Y'
       AND message_name(+) = 'AME_EMAIL_PO'
       and  displayed_field='Approved'     
order by poh.po_header_id desc     

If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification.
If mail_status is SENT, its means mailer has sent email
If mail_status is Null & status is NULL or OPEN, its means that no need to send email as notification preference of user is "Don't send email" or the Workflow mailer s not Up and Running
 

Invoices, Purchase Orders, Suppliers and Payments - Single Query

Hi Viewers,

There is a need to know the PO Matched Invoices and Supplier and Payment details along with Invoice totals and PO totals in a single query for audit purpose.

The below query will give you the details .

 Please replace ORG_ID with Operating unit ID to set the Org context
EXEC mo_global.set_policy_context('S','ORG_ID');

SELECT details.org_id,
       details.vendor_id,
       details.vendor_number,
       details.vendor_name,
       details.vendor_site_id,
       details.vendor_site_code,
       details.tolerance_name,
       details.addressee,
       details.invoice_id,
       details.invoice_num,
       details.inv_creation_date,
       details.payment_method_code,
       details.invoice_amount inv_total_amount,
       details.inv_item_total,
       details.inv_tax_total,
       details.inv_freight_total,
       details.check_number,
       details.payment_date,
       details.po_header_id,
       details.po_creation_date,
       details.po_number,
       (NVL (details.po_amount, 0) + NVL (details.po_tax_amount, 0) + NVL (details.po_freight_amount, 0)) po_total_amount,
       details.po_amount po_items_total,
       details.po_tax_amount po_tax_total,
       details.po_freight_amount po_freight_total,
       details.Matched_amount_to_invoice      
  FROM (  SELECT aia.org_id,
                 asu.vendor_id,
                 asu.segment1 vendor_number,
                 asu.vendor_name,
                 assa.vendor_site_id,
                 assa.vendor_site_code,
                 att.tolerance_name,
                 hps.addressee,
                 aia.INVOICE_ID,
                 aia.INVOICE_NUM,
                 aia.creation_date inv_creation_date,
                 aia.invoice_amount,
                 AP_INVOICES_UTILITY_PKG.get_item_total ( aia.invoice_id, aia.org_id) inv_item_total,
                 (SELECT SUM (NVL (amount, 0)) - SUM (NVL (included_tax_amount, 0))
                    FROM ap_invoice_lines_all aila
                   WHERE aila.invoice_id = aia.invoice_id
                         AND line_type_lookup_code IN ('TAX'))
                    inv_tax_total,
                 AP_INVOICES_UTILITY_PKG.get_freight_total ( aia.invoice_id, aia.org_id) inv_freight_total,
                 aca.CHECK_NUMBER "CHECK_NUMBER",
                 TO_CHAR (TRUNC (aca.CHECK_DATE)) "PAYMENT_DATE",
                 poh.po_header_id,
                 poh.creation_date po_creation_date,
                 AP_INVOICES_UTILITY_PKG.get_po_number (aia.invoice_id) Po_Number,
                 (SELECT SUM (pll.unit_price * pll.quantity)
                    FROM po_lines_all pll, po_line_types ptt
                   WHERE     pll.po_header_id = poh.po_header_id
                         AND pll.line_type_id = ptt.line_type_id
                         AND ptt.line_type <> 'Freight')
                    po_amount,
                 (SELECT SUM (aida.amount)
                    FROM ap_invoice_distributions_all aida, po_distributions_all pda
                   WHERE     1 = 1
                         AND invoice_id = aia.invoice_id
                         AND aida.po_distribution_id = pda.po_distribution_id
                         AND pda.po_header_id = poh.po_header_id                        
                         AND NVL (tax_recoverable_flag, 'Y') <> 'N')
                    Matched_amount_to_invoice,
                 (SELECT SUM (NVL (pda.recoverable_tax, 0) + NVL (pda.nonrecoverable_tax, 0))
                    FROM po_distributions_all pda, po_lines_all pla
                   WHERE     pda.po_header_id = poh.po_header_id
                         AND pla.po_header_id = poh.po_header_id
                         AND pda.po_line_id = pla.po_line_id
                         AND NVL (pla.cancel_flag, 'N') <> 'Y')
                    po_tax_amount,
                 (SELECT SUM (pll.unit_price * pll.quantity)
                    FROM po_lines_all pll, po_line_types ptt
                   WHERE     po_header_id = poh.po_header_id
                         AND pll.line_type_id = ptt.line_type_id
                         AND NVL (pll.cancel_flag, 'N') <> 'Y'
                         AND ptt.line_type = 'Freight')
                    po_freight_amount,
                 aia.payment_method_code
            FROM AP_INVOICES_ALL aia,
                 ap_suppliers asu,
                 ap_supplier_sites_all assa,
                 hz_party_sites hps,
                 po_headers_all poh,
                 AP_TOLERANCE_TEMPLATES ATT,
                 AP_INVOICE_PAYMENTS_ALL aipa,
                 AP_CHECKS_ALL aca
           WHERE     AP_INVOICES_UTILITY_PKG.get_po_number (aia.invoice_id) <> 'UNMATCHED'
                 AND aia.vendor_id = asu.vendor_id
                 AND aia.vendor_site_id = assa.vendor_site_id
                 AND asu.vendor_id = assa.vendor_id
                 AND assa.party_site_id = hps.party_site_id
                 AND assa.tolerance_id = att.tolerance_id
                 AND AP_INVOICES_UTILITY_PKG.get_po_number (aia.invoice_id) = poh.segment1
                 AND aia.INVOICE_ID = aipa.INVOICE_ID(+)
                 AND aipa.CHECK_ID = aca.CHECK_ID(+)
                 AND NVL (aipa.reversal_flag, 'N') <> 'Y'
        ORDER BY asu.segment1,
                 hps.addressee,
                 poh.segment1,
                 aia.invoice_num,
                 aia.creation_date) details
 WHERE 1 = 1